TRY_TO_DOUBLE

Safely cast string values to double precision. If the conversion fails, the expression returns NULL instead of raising an error.

Basic Usage

Snowflake

TRY_TO_DOUBLE(expr [, format]) is available on Snowflake only.

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

Databricks

SELECT
  TRY_CAST('40.7128' AS DOUBLE) AS lat,
  TRY_CAST('N/A' AS DOUBLE) AS invalid_lat;
-- Returns: 40.7128, NULL

BigQuery

SELECT
  SAFE_CAST('40.7128' AS FLOAT64) AS lat,
  SAFE_CAST('N/A' AS FLOAT64) AS invalid_lat;
-- Returns: 40.7128, NULL

JSON Coordinates

When coordinates are stored as strings in JSON:

Snowflake

SELECT
  TRY_TO_DOUBLE(variant_col:"lat"::string) AS latitude,
  TRY_TO_DOUBLE(variant_col:"lon"::string) AS longitude
FROM my_table;

Databricks

Extract the JSON field as a string, then cast:

SELECT
  TRY_CAST(get_json_object(json_col, '$.lat') AS DOUBLE) AS latitude,
  TRY_CAST(get_json_object(json_col, '$.lon') AS DOUBLE) AS longitude
FROM my_table;

BigQuery

SELECT
  SAFE_CAST(JSON_VALUE(json_col, '$.lat') AS FLOAT64) AS latitude,
  SAFE_CAST(JSON_VALUE(json_col, '$.lon') AS FLOAT64) AS longitude
FROM my_table;

In Coalesce Transforms

Snowflake

TRY_TO_DOUBLE({{SRC}})

Expanded:

TRY_TO_DOUBLE("ORDERS"."LATITUDE")

Databricks

TRY_CAST({{SRC}} AS DOUBLE)

Expanded:

TRY_CAST(`orders`.`latitude` AS DOUBLE)

BigQuery

SAFE_CAST({{SRC}} AS FLOAT64)

Expanded:

SAFE_CAST(`orders`.`latitude` AS FLOAT64)

Snowflake Syntax Reference

TRY_TO_DOUBLE(expr [, format])

• expr: Required. A string VARCHAR or CHAR expression to convert.
• format: Optional. A format model for strings with consistent formatting such as commas. See SQL format models.

Best Practices

• Input type: Only cast from strings when the column is character data. Numeric columns do not need a safe cast function.
• VARIANT / JSON on Snowflake: Convert JSON fields to string first (::string) before applying TRY_TO_DOUBLE.
• Error rates: If many conversions fail, performance may degrade. These functions work best when errors are rare.
• Format usage (Snowflake): Use the optional format model only when input strings have consistent formatting, for example, 1,234.56.
• Precision caveat: Conversion from string to floating point may involve rounding typical in floating-point representation.

What's Next?

• Snowflake TRY_TO_DOUBLE
• Helper Tokens
• Databricks TRY_CAST
• BigQuery SAFE_CAST