TRY_CAST_FLOAT

Attempt to cast a value to FLOAT (DOUBLE), returning NULL on failure instead of raising an error.

Category: miscReturns: DOUBLEDialect: PostgreSql

Syntax

TRY_CAST_FLOAT(expr)

Description

## Overview Attempts to convert the input value to FLOAT (double precision). Returns the parsed number on success and NULL on failure, instead of raising the type error that a regular CAST would produce. Use this function to parse untrusted numeric columns from CSV, JSON, or string-typed staging tables without risking query failure. ## Behavior - Returns NULL if the input is NULL. - Returns NULL if the input cannot be parsed as a finite DOUBLE. - Accepts integer and decimal forms and scientific notation with `e`/`E`. - Accepts a leading sign (`+` or `-`). - Leading and trailing whitespace is tolerated. - Rejects thousands separators and currency symbols. - Deterministic and side effect free. ## Compatibility - PG-compat TRY_CAST semantics for DOUBLE, offered as a named helper function.

Parameters

Name	Type	Description
`expr`		Specifies the value to attempt to cast to FLOAT (double precision).

Examples

-- Decimal string
SELECT TRY_CAST_FLOAT('3.14');  -- 3.14

-- Integer string parses as DOUBLE
SELECT TRY_CAST_FLOAT('42');  -- 42.0

-- Scientific notation
SELECT TRY_CAST_FLOAT('1.5e3');  -- 1500.0

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

-- Safe numeric parsing from stage to curated
SELECT id, TRY_CAST_FLOAT(price_text) AS price
FROM stage.raw.products
WHERE TRY_CAST_FLOAT(price_text) IS NOT NULL;

-- Quarantine rows that fail to parse
SELECT id, price_text FROM stage.raw.products
WHERE price_text IS NOT NULL AND TRY_CAST_FLOAT(price_text) IS NULL;

Pitfalls

TRY_CAST_FLOAT returns NULL on failure, not a sentinel value. Downstream arithmetic will propagate NULL, which is usually correct but may surprise aggregations that assume dense data.
Range is not checked. A string that parses to Infinity or -Infinity (for example, `1e500`) returns NULL under DeltaForge semantics even though some engines produce Infinity. Validate extreme magnitudes upstream if needed.
Decimal separator is `.` only; `,` is not accepted. Normalize locale-dependent formats before calling the function.
NaN handling is engine-dependent; DeltaForge rejects `NaN` and returns NULL. Do not rely on the function to produce IEEE NaN values.