Oracle TO_BINARY_DOUBLE() Function

Oracle TO_BINARY_DOUBLE() is a built-in function that converts the given expression to a double precision floating point number.

Oracle `TO_BINARY_DOUBLE()` Syntax

Here is the syntax for the Oracle TO_BINARY_DOUBLE() function:

TO_BINARY_DOUBLE(expr [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

Parameters

expr: Required. It can be any expression that evaluates to a character string of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2, a number of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null. You cannot use floating-point format elements (F, f, D, or d) in a character string expr.
DEFAULT return_value ON CONVERSION ERROR: Optional. It allows you to specify the value to be returned when a conversion error occurs. return_value can be an expression or a bind variable, and must evaluate to a character string of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2, a number of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null. The function converts return_value to BINARY_DOUBLE in the same way that it converts expr to BINARY_DOUBLE. If return_value cannot be converted to BINARY_DOUBLE, the function returns an error.
fmt: Optional. A format string.
'nlsparam': Optional. You can use this parameter to set 'NLS_DATE_LANGUAGE = language' form, where language is the language name.

Return Value

The Oracle TO_BINARY_DOUBLE() function returns a double precision floating point number.

If expr is BINARY_DOUBLE, the function returns expr. If expr evaluates to null, the function returns null. Otherwise, the function converts expr to a BINARY_DOUBLE value.

If expr or return_value evaluates to one of the following character strings, the function converts it as follows:

The case-insensitive string “INF” is converted to positive infinity.
The case-insensitive string “-INF” is converted to negative infinity.
The case-insensitive string “NaN” is converted to NaN (not a number).

Note:

Conversion from character strings or NUMBER to BINARY_DOUBLE may not be exact, because NUMBER and character types represent numeric values with decimal precision, whereas BINARY_DOUBLE uses binary precision.
Conversion from BINARY_FLOAT to BINARY_DOUBLE is exact.

Oracle `TO_BINARY_DOUBLE()` Examples

Here are some examples that demonstrate the usage of the Oracle TO_BINARY_DOUBLE() function.

Basic Usage

The following statement shows the basic usage of the Oracle TO_BINARY_DOUBLE() function:

SELECT
    TO_BINARY_DOUBLE(1)
FROM dual;

输出：

TO_BINARY_DOUBLE(1)
______________________
1.0

`INF` and `-INF`

The Oracle TO_BINARY_DOUBLE() function converts the case-insensitive strings “INF” and “-INF” to positive infinity and negative infinity, respectively.

SELECT
    TO_BINARY_DOUBLE('INF') "INF",
    TO_BINARY_DOUBLE('-INF') "-INF"
FROM dual;

输出：

INF         -INF
___________ ____________
Infinity    -Infinity

NaN

The Oracle TO_BINARY_DOUBLE() function converts the case-insensitive string “NaN” to NaN (not a number).

SELECT
    TO_BINARY_DOUBLE('NaN') "NaN",
    TO_BINARY_DOUBLE('nan') "nan",
    TO_BINARY_DOUBLE('NAN') "NAN"
FROM dual;

输出：

NaN    nan    NAN
______ ______ ______
NaN    NaN    NaN

NULL Argument

If any argument is NULL, TO_BINARY_DOUBLE() returns NULL.

SET NULL 'NULL';
SELECT
    TO_BINARY_DOUBLE(NULL)
FROM dual;

输出：

TO_BINARY_DOUBLE(NULL)
_________________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle TO_BINARY_DOUBLE() is a built-in function that converts the given expression to a double precision floating point number.

Oracle TO_BINARY_DOUBLE() Syntax