TRY_TO_DOUBLE
TRY_TO_DOUBLE(expr [, format]) attempts to cast a string expression to double-precision floating-point. If the conversion fails, it returns NULL instead of raising an error.
Syntax and Parameters
TRY_TO_DOUBLE(expr [, format])
expr: (required) A string (VARCHAR/CHAR) expression to convert.- Must be of character type.
- Returns
NULLif it cannot be parsed as a valid number.
format:(optional) A format model that describes how to parse the input string.- Use this when input strings use formatting such as commas.
- See SQL format models for details.
Behavior & Return Type
- Returns value of type
FLOAT(Snowflake’s double-precision floating point). - Supports input strings that are decimal integers, fractional numbers, scientific notation, as well as special values like
nan,inf,infinity. - Returns
NULLon conversion error or invalid input, instead of throwing an exception.
Basic Usage
SELECT
TRY_TO_DOUBLE('40.7128') AS lat,
TRY_TO_DOUBLE('-74.0060') AS lon;
-- Returns: 40.7128, -74.0060
SELECT
TRY_TO_DOUBLE('N/A') AS lat,
TRY_TO_DOUBLE('180W') AS lon;
-- Returns: NULL, NULL
Using TRY_TO_DOUBLE with JSON (VARIANT)
When storing coordinates as strings in JSON within a VARIANT column, you must extract the field and cast it to string before using TRY_TO_DOUBLE:
SELECT
TRY_TO_DOUBLE(variant_col:"lat"::string) AS latitude,
TRY_TO_DOUBLE(variant_col:"lon"::string) AS longitude
FROM my_table;
info
variant_col:"lat" is a VARIANT expression; ::string converts it to VARCHAR, which TRY_TO_DOUBLE can accept.
Example:
SELECT
TRY_TO_DOUBLE(PARSE_JSON('{"lat": "40.7128", "lon": "-74.0060"}'):"lat"::string) AS latitude,
TRY_TO_DOUBLE(PARSE_JSON('{"lat": "40.7128", "lon": "-74.0060"}'):"lon"::string) AS longitude;
Result:
latitude longitude
--------- ----------
40.7128 -74.0060
Using TRY_TO_DOUBLE in Coalesce
When writing transformations in Coalesce, use can use a helper token:
TRY_TO_DOUBLE({{SRC}})
{{SRC}} expands to the full source node and column, e.g.:
"ORDERS"."LATITUDE"→TRY_TO_DOUBLE("ORDERS"."LATITUDE")"CUSTOMER"."LONGITUDE"→TRY_TO_DOUBLE("CUSTOMER"."LONGITUDE")
Best Practices
- Input type: Only accepts string expressions. If your column is already numeric (
NUMBER,FLOAT,DOUBLE), you don’t needTRY_TO_DOUBLE. - VARIANT handling: For JSON / VARIANT types, convert fields to string first (
::string) before applyingTRY_TO_DOUBLE. - Error rates: If many conversions fail performance may degrade—these functions are optimized for cases where errors are rare.
- Format usage: Use the optional format model only when input strings have consistent formatting, for example, 1,234.56.
- Precision caveat: Conversion from string to FLOAT may involve rounding or precision shifts typical in floating-point representation.