TO_DECIMAL , TO_NUMBER , TO_NUMERIC¶

Syntax¶

TO_DECIMAL( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMBER( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMERIC( <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¶

• For NULL input, the result is NULL.

• Fixed-point numbers with different scale are converted by either adding zeros to the right (when increasing scale) or by reducing the number of fractional digits by rounding. Note that fixed-to-fixed casts that increase scale might fail.

• Floating-point numbers are converted if they are within the representable range given the scale; the conversion between binary and decimal fractional numbers is not precise; loss of precision or out-of-range errors might result. Infinities and not-a-numbers result in conversion error.

• Strings are converted as decimal integer or fractional numbers.

• For VARIANT input:

  • If the variant contains a fixed-point or a floating point numeric value, an appropriate numeric conversion is performed.

  • If the variant contains a string, a string conversion is performed.

  • If the variant contains a Boolean value, the result is 0 or 1 (for false and true, correspondingly).

  • If the variant contains JSON null value, the output is NULL.

Examples¶

create or replace table number_conv(expr varchar);
insert into number_conv values ('12.3456'), ('98.76546');

select expr, to_number(expr),  to_number(expr, 10, 1), to_number(expr, 10, 8) from number_conv;

+----------+-----------------+------------------------+------------------------+
| EXPR     | TO_NUMBER(EXPR) | TO_NUMBER(EXPR, 10, 1) | TO_NUMBER(EXPR, 10, 8) |
|----------+-----------------+------------------------+------------------------|
| 12.3456  |              12 |                   12.3 |            12.34560000 |
| 98.76546 |              99 |                   98.8 |            98.76546000 |
+----------+-----------------+------------------------+------------------------+

select expr, to_number(expr, 10, 9) from number_conv;

100039 (22003): Numeric value '12.3456' is out of range

select column1,
       to_decimal(column1, '99.9') as D0,
       to_decimal(column1, '99.9', 9, 5) as D5,
       to_decimal(column1, 'TM9', 9, 5) as TD5
from values ('1.0'), ('-12.3'), ('0.0'), ('  - 0.1   ');

+---------+-----+-----------+-----------+
| COLUMN1 |  D0 |        D5 |       TD5 |
|---------+-----+-----------+-----------|
| 1.0     |   1 |   1.00000 |   1.00000 |
| -12.3   | -12 | -12.30000 | -12.30000 |
| 0.0     |   0 |   0.00000 |   0.00000 |
| - 0.1   |   0 |  -0.10000 |  -0.10000 |
+---------+-----+-----------+-----------+