Skip to main content

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 NULL if 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 NULL on 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 need TRY_TO_DOUBLE.
  • VARIANT handling: For JSON / VARIANT types, convert fields to string first (::string) before applying TRY_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.

What's Next?​