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>' ] )
Copy

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 might 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 might 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;
Copy

Output:

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