TRY_CAST_INT

Attempt to cast a value to INT, returning NULL on failure instead of raising an error.

Category: miscReturns: INTDialect: PostgreSql

Syntax

TRY_CAST_INT(expr)

Description

## Overview Attempts to convert the input value to INT. Returns the parsed integer on success and NULL on failure, instead of raising the type error that a regular CAST would produce. Use this function to parse untrusted integer columns in staging data without risking query failure. ## Behavior - Returns NULL if the input is NULL. - Returns NULL if the input cannot be parsed as an integer that fits in the INT range. - Accepts a leading sign (`+` or `-`). - Fractional input is truncated toward zero (`'3.7'` becomes 3, `'-3.7'` becomes -3). - Scientific notation is not accepted; use TRY_CAST_FLOAT first if you need to handle it. - Leading and trailing whitespace is tolerated. - Deterministic and side effect free. ## Compatibility - PG-compat TRY_CAST semantics for INT, offered as a named helper function.

Parameters

Name	Type	Description
`expr`		Specifies the value to attempt to cast to INT. Accepts signed decimal integer strings and numeric inputs.

Examples

-- Integer string
SELECT TRY_CAST_INT('42');  -- 42

-- Decimal string truncates toward zero
SELECT TRY_CAST_INT('3.7');  -- 3

-- Unparseable string returns NULL
SELECT TRY_CAST_INT('abc');  -- NULL

-- Safe integer parsing from stage
SELECT id, TRY_CAST_INT(quantity_text) AS quantity
FROM stage.raw.orders
WHERE TRY_CAST_INT(quantity_text) IS NOT NULL;

-- Overflow beyond INT range returns NULL
SELECT TRY_CAST_INT('9999999999999');  -- NULL

-- Combine with COALESCE for a default
SELECT COALESCE(TRY_CAST_INT(raw_qty), 0) AS qty FROM stage.raw.orders;

Pitfalls

Fractional values are truncated toward zero, not rounded. `TRY_CAST_INT('2.9')` returns 2, not 3. Apply ROUND first if you want bankers rounding or a specific mode.
Values outside the INT range return NULL rather than overflowing. To parse wider values, use TRY_CAST with BIGINT or apply TRY_CAST_FLOAT first.
Scientific notation is rejected. `TRY_CAST_INT('1e3')` returns NULL; use TRY_CAST_FLOAT and then cast to INT if you need to accept it.
Thousands separators (`1,000`) are not accepted. Strip them with REPLACE before calling.