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 acceptsan optional format model. Format models are described atSQL format models. The format modelspecifies the format of the input string, not the format of theoutput value.

Returns¶

This function returns a value of FLOAT data type.

Ifexpr is NULL, the function returns NULL.

Usage notes¶

• Fixed-point numbers are converted to floating point; the conversioncannot 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 conversionwill be performed.

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

  • If the variant contains a string, a string conversion will beperformed.

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

  • If the variant contains JSONnull value, the output will beNULL.

• Conversion of decimal fractions to binary float and back is not precise(that is, printing of a floating-point number converted from decimal representationmight produce a slightly different number). If precise representation of decimalfractions is required, use fixed-point numbers.

Examples¶

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

CREATEORREPLACETABLEdouble_demo(dDECIMAL(7,2),vVARCHAR,oVARIANT);INSERTINTOdouble_demo(d,v,o)SELECT1.1,'2.2',TO_VARIANT(3.14);SELECTTO_DOUBLE(d),TO_DOUBLE(v),TO_DOUBLE(o)FROMdouble_demo;

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

The following example shows that converting from a binary float back to a number is not precise:

SELECTTO_DOUBLE(1.1)::NUMBER(38,18);

+--------------------------------+| TO_DOUBLE(1.1)::NUMBER(38, 18) ||--------------------------------||           1.100000000000000089 |+--------------------------------+