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(precision, 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.

  • For fixed-point numbers:

    • Numbers with different scales are converted by either adding zeros to the right (if the scale needs to be increased) or by reducing the number of fractional digits by rounding (if the scale needs to be decreased).

    • Note that casts of fixed-point numbers to fixed-point numbers that increase scale might fail.

  • For floating-point numbers:

    • Numbers are converted if they are within the representable range, given the scale.

    • The conversion between binary and decimal fractional numbers is not precise. This might result in loss of precision or out-of-range errors.

    • Values of infinity and NaN (not-a-number) result in conversion errors.

  • Strings are converted as decimal, integer, fractional, or floating-point numbers.

    • For fractional input, the precision is deduced as the number of digits after the point.

    • For floating-point input, omitting the mantissa or exponent is allowed and is interpreted as 0. Thus, E is parsed as 0.

  • 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