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> ] ] )

Arguments

Required:

expr

An expression of a numeric, character, or variant type.

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

  • Only works for string expressions.

Examples

The following examples demonstrate usage of TRY_TO_DECIMAL:

This first example fails because the last column (“dec_with_range_error”) does not store enough significant digits to hold the value that it’s 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');
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');
+-------------+-----+----------------+--------------------+
| 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, not just digits and decimal points, but the format specifier for the last column does not tell the TO_DECIMAL function to expect the dollar sign.

SELECT column1 AS orig_string,
       TO_DECIMAL(column1, '$999.00') AS num,
       TO_DECIMAL(column1, '$999.00', 5, 2) AS num_with_scale,
       TO_DECIMAL(column1, 5, 2) AS num_with_format_err
  FROM VALUES ('$345.12');
100038 (22018): Numeric value '$345.12' 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, '$999.00') AS num,
       TRY_TO_DECIMAL(column1, '$999.00', 5, 2) AS num_with_scale,
       TRY_TO_DECIMAL(column1, 5, 2) AS num_with_format_err
  FROM VALUES ('$345.12');
+-------------+-----+----------------+---------------------+
| ORIG_STRING | NUM | NUM_WITH_SCALE | NUM_WITH_FORMAT_ERR |
|-------------+-----+----------------+---------------------|
| $345.12     | 345 |         345.12 |                NULL |
+-------------+-----+----------------+---------------------+