类别：

转换函数

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。

返回

该函数返回 NUMBER 类型的值，其默认规则如下：

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

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

如果无法执行转换或输入为 NULL，则返回 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 |
+-------------+------+----------------+---------------------+

以下示例失败，因为输入表达式包含不是数字的字符：

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 |
+-------------+------+

您可以通过在 TO_DECIMAL 或 TRY_TO_DECIMAL 函数中指定 X 格式元素，将十六进制值转换为十进制值：

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 |
+-------------+----------------+--------------------+

有关其他示例，请参阅 TO_DECIMAL、TO_NUMBER、TO_NUMERIC。

语言: 中文