Categories:

Conversion functions

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC¶

A special version of TO_DECIMAL , TO_NUMBER , TO_NUMERIC that performs the same operation (i.e. converts an input expression to a fixed-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

These functions are synonymous.

For more information, see Error-handling conversion functions.

Syntax¶

TRY_TO_DECIMAL( <string_expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TRY_TO_NUMBER( <string_expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TRY_TO_NUMERIC( <string_expr> [, '<format>' ] [, <precision> [, <scale> ] ] )
Copy

Arguments¶

Required:

string_expr

An expression of type VARCHAR.

Optional:

format

The SQL format model used to parse the input expr and return. For more information, see SQL format models.

precision

The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).

scale

The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number). The default scale is 0.

Returns¶

The function returns NUMBER(p,s), where p is the precision and s is the scale.

If the precision is not specified, then it defaults to 38.

If the scale is not specified, then it defaults to 0.

Usage notes¶

  • The input must be a string expression.

Examples¶

This first example fails because the last column (dec_with_range_error) does not store enough significant digits to hold the value that it is asked to hold.

SELECT column1 AS orig_string,
       TO_DECIMAL(column1) AS dec,
       TO_DECIMAL(column1, 10, 2) AS dec_with_scale,
       TO_DECIMAL(column1, 4, 2) AS dec_with_range_err
  FROM VALUES ('345.123');
Copy
100039 (22003): Numeric value '345.123' is out of range

The next query is the same as the preceding query, except that it uses TRY_TO_DECIMAL rather than TO_DECIMAL, so it converts the out-of-range value to NULL.

SELECT column1 AS orig_string,
       TRY_TO_DECIMAL(column1) AS dec,
       TRY_TO_DECIMAL(column1, 10, 2) AS dec_with_scale,
       TRY_TO_DECIMAL(column1, 4, 2) AS dec_with_range_err
  FROM VALUES ('345.123');
Copy
+-------------+-----+----------------+--------------------+
| ORIG_STRING | DEC | DEC_WITH_SCALE | DEC_WITH_RANGE_ERR |
|-------------+-----+----------------+--------------------|
| 345.123     | 345 |         345.12 |               NULL |
+-------------+-----+----------------+--------------------+

The next example fails because the input string contains a dollar sign and a comma to separate groups of digits, not just digits and decimal points. However, the format specifier for the last column does not tell the TO_DECIMAL function to expect the dollar sign and comma.

SELECT column1 AS orig_string,
       TO_DECIMAL(column1, '$9,999.00') AS num,
       TO_DECIMAL(column1, '$9,999.00', 6, 2) AS num_with_scale,
       TO_DECIMAL(column1, 6, 2) AS num_with_format_err
  FROM VALUES ('$7,543.21');
Copy
100038 (22018): Numeric value '$7,543.21' is not recognized

The next query is the same as the preceding query, except that it uses TRY_TO_DECIMAL rather than TO_DECIMAL, so it converts the input to NULL.

SELECT column1 AS orig_string,
       TRY_TO_DECIMAL(column1, '$9,999.00') AS num,
       TRY_TO_DECIMAL(column1, '$9,999.00', 6, 2) AS num_with_scale,
       TRY_TO_DECIMAL(column1, 6, 2) AS num_with_format_err
  FROM VALUES ('$7,543.21');
Copy
+-------------+------+----------------+---------------------+
| ORIG_STRING |  NUM | NUM_WITH_SCALE | NUM_WITH_FORMAT_ERR |
|-------------+------+----------------+---------------------|
| $7,543.21   | 7543 |        7543.21 |                NULL |
+-------------+------+----------------+---------------------+
Language: English