Converts an expression to a double-precision floating-point number.
For NULL input, the result is NULL.
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.
This function returns a value of FLOAT data type.
If expr is NULL, the function returns NULL.
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.
Conversion of decimal fractions to binary float and back is not precise (that is, 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.
After creating a table with columns of different data types, this script calls TO_DOUBLE on each of those columns:
CREATE OR REPLACE 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
+--------------+--------------+--------------+ | 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:
SELECT TO_DOUBLE(1.1)::NUMBER(38, 18);
Copy
+--------------------------------+ | TO_DOUBLE(1.1)::NUMBER(38, 18) | |--------------------------------| | 1.100000000000000089 | +--------------------------------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4