Categories:

Conversion Functions

TO_DECIMAL , TO_NUMBER , TO_NUMERIC

Converts an input expression to a fixed-point number. For NULL input, the output is NULL.

These functions are synonymous.

See also:

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

Syntax

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

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

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

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
Copy
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 |
+---------+-----+-----------+-----------+
Copy
Language: English