Oracle TO_BINARY_FLOAT Function: A Simple Guide

The TO_BINARY_FLOAT function in Oracle SQL is a conversion function that changes a value (like a NUMBER or a VARCHAR2 string) into a single-precision floating-point number (BINARY_FLOAT).

This is the smaller, less-precise sibling of TO_BINARY_DOUBLE. It's used for scientific calculations where storage or performance is more critical than high precision. A key feature is its ability to handle conversion errors gracefully using a DEFAULT clause.

What is the TO_BINARY_FLOAT Function in Oracle?

The TO_BINARY_FLOAT(expr) function takes an expression and converts it to the BINARY_FLOAT data type.

• Input: The expression can be a string (VARCHAR2), a standard NUMBER, a BINARY_DOUBLE, a BOOLEAN (TRUE becomes 1, FALSE becomes 0), or NULL.
• Error Handling: Its most powerful feature is the optional DEFAULT ... ON CONVERSION ERROR clause. This lets you specify a value (like 0) to return if the conversion fails (e.g., converting 'N/A'), preventing your query from stopping with an error.

TO_BINARY_FLOAT Function Syntax

The syntax for TO_BINARY_FLOAT is:

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

Let's break that down:

• expr: The value or column you want to convert.
• DEFAULT return_value ON CONVERSION ERROR (Optional): Tells Oracle what to return if the conversion fails (e.g., 0 or NULL).
• 'fmt' / 'nlsparam' (Optional): Format models, just like in TO_NUMBER, used when converting a string.

Oracle TO_BINARY_FLOAT Function Examples

Here are two practical examples of how to use TO_BINARY_FLOAT.

Example 1: Converting a NUMBER to BINARY_FLOAT using TO_BINARY_FLOAT

This example takes a standard NUMBER and converts it to the BINARY_FLOAT type. The output is shown in scientific notation.

Query:

SELECT 
  1234.56 AS "Original_Number",
  TO_BINARY_FLOAT(1234.56) AS "Single_Precision"
FROM DUAL;

Result: (The exact output for the single-precision value may vary slightly by system)

Original_Number Single_Precision
--------------- ----------------
        1234.56       1.2346E+03

Example 2: Handling Conversion Errors using TO_BINARY_FLOAT

This is the function's most powerful feature. If you try to convert an invalid string like '2oo', a normal query would fail. This function can return a default value instead.

Query:

SELECT 
  TO_BINARY_FLOAT('2oo' DEFAULT 0 ON CONVERSION ERROR) AS "Converted_Value"
FROM DUAL;

Result: (Instead of an error, the query returns the default value 0.)

Converted_Value
---------------
              0