Return true if the input string parses as a signed integer or decimal number.
IS_NUMERIC(str)## Overview Returns TRUE if the input string can be parsed as a decimal number, FALSE otherwise, and NULL for a NULL input. Use this function when you want a broader validator than IS_INTEGER, for example to accept prices, rates, or scientific measurements in string form. ## Validation rules - Optional leading `+` or `-` sign. - One or more decimal digits, optionally followed by a `.` and more decimal digits. - Optional exponent with `e` or `E`, a sign, and one or more digits. - No thousands separators and no currency symbols; strip those upstream. - Leading and trailing whitespace is rejected; TRIM first if needed. - Special values like `Infinity`, `NaN`, and `inf` are rejected. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Range is not checked; extremely large magnitudes may pass validation but overflow on cast to DOUBLE. ## Compatibility - Mirrors the PG-compat IS_NUMERIC contract: structural parse only, no range check.
| Name | Type | Description |
|---|---|---|
str | Specifies the string to test for numeric parseability. Accepts integers, decimals, and scientific notation. |
-- Integer string
SELECT IS_NUMERIC('42'); -- true-- Decimal string
SELECT IS_NUMERIC('3.14'); -- true-- Signed negative decimal
SELECT IS_NUMERIC('-99.5'); -- true-- Scientific notation
SELECT IS_NUMERIC('1.5e3'); -- true-- Non-numeric string
SELECT IS_NUMERIC('abc'); -- false-- Filter numeric values out of a mixed column
SELECT raw_value
FROM stage.raw.values
WHERE IS_NUMERIC(raw_value);