Oracle TO_BINARY_FLOAT() Function

Oracle TO_BINARY_FLOAT() is a built-in function that converts the given expression to a binary float.

Oracle `TO_BINARY_FLOAT()` Syntax

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

TO_BINARY_FLOAT(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 numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null. You cannot use floating-point format elements (F, f, D, or d) in the character string expr.
DEFAULT return_value ON CONVERSION ERROR: Optional. It allows you to specify a value to return if a conversion error occurs. return_value can be an expression or a bind variable, and it must evaluate to a character string of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2, a numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null. The function converts return_value to a BINARY_DOUBLE in the same way as 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 the parameter using the 'NLS_DATE_LANGUAGE = language' form, where language is the language name.

Return Value

The Oracle TO_BINARY_FLOAT() function returns a binary float.

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 the following character strings, the function converts them 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 precise because NUMBER and character types represent numeric values using decimal precision, whereas BINARY_DOUBLE uses binary precision.
Conversion from BINARY_FLOAT to BINARY_DOUBLE is precise.

Oracle `TO_BINARY_FLOAT()` Examples

Here are a few examples that demonstrate the usage of the Oracle TO_BINARY_FLOAT() function.

Basic Usage

The following statement demonstrates the basic usage of the Oracle TO_BINARY_FLOAT() function:

SELECT
    TO_BINARY_FLOAT(1)
FROM dual;

输出：

TO_BINARY_FLOAT(1)
_____________________
1.0

`INF` and `-INF`

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

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

输出：

INF         -INF
___________ ____________
Infinity    -Infinity

NaN

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

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

输出：

NaN    nan    NAN
______ ______ ______
NaN    NaN    NaN

NULL Argument

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

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

输出：

TO_BINARY_FLOAT(NULL)
________________________
NULL

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

Conclusion

Oracle TO_BINARY_FLOAT() is a built-in function that converts the given expression to a binary float.

Oracle TO_BINARY_FLOAT() Syntax