类别：: 转换函数

TRY_TO_DECIMAL、TRY_TO_NUMBER、TRY_TO_NUMERIC¶

A special version of TO_DECIMAL、TO_NUMBER、TO_NUMERIC that performs the same operation of converting an input expression to a fixed-point number, but has error-handling support so that the function returns NULL if the conversion can't be performed.

这些函数为同义函数。

有关更多信息，请参阅错误处理转换函数。

语法¶

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

实参¶

必填：

string_expr: 类型 VARCHAR 的表达式。

可选：

format: 用于解析输入 expr 和返回的 SQL 格式模型。有关更多信息，请参阅 SQL 格式模型。
precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used to determine the number of bytes that are needed to store the number and doesn't have any effect on efficiency, so the default is the maximum (38).
scale: 小数十进制位数（从 0 到 precision - 1）。0 表示没有小数位数（即整数）。默认小数位数为 0。

返回¶

The function returns a value of type NUMBER with the following defaults:

If the precision isn't specified, then it defaults to 38.
If the scale isn't specified, then it defaults to 0.

If the conversion can't be performed or the input is NULL, returns NULL.

使用说明¶

输入必须是字符串表达式。

示例¶

The following example fails because the last column (dec_with_range_error) doesn't 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 following 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 following 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 doesn't 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 following 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 |
+-------------+------+----------------+---------------------+

The following example fails because the input expression contains characters that aren't digits:

SELECT column1 AS orig_string,
       TO_DECIMAL(column1) AS num
  FROM VALUES ('aaa');

Copy

100038 (22018): Numeric value 'aaa' is not recognized

The following 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) AS num
  FROM VALUES ('aaa');

Copy

+-------------+------+
| ORIG_STRING | NUM  |
|-------------+------|
| aaa         | NULL |
+-------------+------+

You can perform the conversion if you specify the X format element with the TO_DECIMAL or TRY_TO_DECIMAL function to convert a hexadecimal value to a decimal value:

SELECT column1 AS orig_string,
       TO_DECIMAL(column1, 'XXX') AS to_decimal_num,
       TRY_TO_DECIMAL(column1, 'XXX') AS try_to_decimal_num
  FROM VALUES ('aaa');

Copy

+-------------+----------------+--------------------+
| ORIG_STRING | TO_DECIMAL_NUM | TRY_TO_DECIMAL_NUM |
|-------------+----------------+--------------------|
| aaa         |           2730 |               2730 |
+-------------+----------------+--------------------+

For additional examples, see TO_DECIMAL、TO_NUMBER、TO_NUMERIC.