IS_INTEGER

Return true if the input string parses as a signed integer with no fractional part.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_INTEGER(str)

Description

## Overview Returns TRUE if the input string represents a signed integer, FALSE otherwise, and NULL for a NULL input. Use this function to validate CSV columns before casting, to separate integer-like strings from decimal or alphanumeric values, or to guard a numeric aggregation. ## Validation rules - Optional leading `+` or `-` sign. - One or more decimal digits with no fractional part, no exponent, and no thousands separators. - Leading and trailing whitespace is rejected; TRIM first if needed. - Empty strings return FALSE. - Scientific notation (`1e3`) returns FALSE; use IS_NUMERIC if you need to accept it. - Range is not bounded to any particular integer width: `IS_INTEGER('9999999999999999999')` returns TRUE even though the value would overflow BIGINT on cast. Pair with TRY_CAST_INT for range checking. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. ## Compatibility - Mirrors the PG-compat IS_INTEGER contract: structural parse only, no overflow check.

Parameters

Name	Type	Description
`str`		Specifies the string to test for integer parseability.

Examples

-- Non-negative integer
SELECT IS_INTEGER('42');  -- true

-- Signed integer
SELECT IS_INTEGER('-100');  -- true

-- Decimal is not an integer
SELECT IS_INTEGER('3.14');  -- false

-- Trailing whitespace fails
SELECT IS_INTEGER('42 ');  -- false

-- Filter numeric strings in a staging table
SELECT raw_value
FROM stage.raw.values
WHERE IS_INTEGER(raw_value);

-- Guard a TRY_CAST
SELECT CASE WHEN IS_INTEGER(raw_qty) THEN CAST(raw_qty AS INT) END AS qty
FROM stage.raw.orders;

Pitfalls

IS_INTEGER does not reject overflow. A 30-digit string passes the check but cannot be stored as INT or BIGINT. Combine with TRY_CAST_INT when you need a value that actually fits.
Scientific notation such as `1e3` returns FALSE even though it represents a whole number. Use IS_NUMERIC followed by a cast if you need to accept it.
Thousands separators (`1,000`) and underscores (`1_000`) are rejected. Strip them with REPLACE before validation if your ingest format uses them.
Whitespace (leading or trailing) fails the check. Wrap the argument in TRIM or CLEAN_SPACES first when working with user-entered data.