Categories:

Conversion Functions

TO_DOUBLE

Converts an expression to a double-precision floating-point number.

For NULL input, the result is NULL.

See also:

TRY_TO_DOUBLE

Syntax

TO_DOUBLE( <expr> [, '<format>' ] )

Arguments

  • expr An expression of a numeric, character, or variant type.

  • format If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.

Usage Notes

  • Fixed-point numbers are converted to floating point; the conversion cannot fail, but may result in loss of precision.

  • Strings are converted as decimal integer or fractional numbers, scientific notation and special values (nan, inf, infinity) are accepted.

  • For VARIANT input:

    • If the variant contains a fixed-point value, the numeric conversion will be performed.

    • If the variant contains a floating-point value, the value will be preserved unchanged.

    • If the variant contains a string, a string conversion will be performed.

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

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

Note that conversion of decimal fractions to binary and back is not precise (i.e. printing of a floating-point number converted from decimal representation may produce a slightly different number). If precise representation of decimal fractions is required, use fixed-point numbers.

Examples

After creating a table with columns of different data types, this script calls TO_DOUBLE on each of those columns:

CREATE TABLE double_demo (d DECIMAL(7, 2), v VARCHAR, o VARIANT);
INSERT INTO double_demo (d, v, o) SELECT 1.1, '2.2', TO_VARIANT(3.14);
SELECT TO_DOUBLE(d), TO_DOUBLE(v), TO_DOUBLE(o) FROM double_demo;

Output:

+--------------+--------------+--------------+
| TO_DOUBLE(D) | TO_DOUBLE(V) | TO_DOUBLE(O) |
|--------------+--------------+--------------|
|          1.1 |          2.2 |         3.14 |
+--------------+--------------+--------------+