Oracle TO_BINARY_DOUBLE Function: A Simple Guide

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

This data type is used for high-precision scientific and engineering calculations. A key feature of this function is its ability to handle conversion errors gracefully using a DEFAULT clause, which prevents your query from stopping when it finds bad data.

What is the TO_BINARY_DOUBLE Function in Oracle?

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

• Input: The expression can be a string (VARCHAR2), a standard NUMBER, a BINARY_FLOAT, 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, for example, if you try to convert the string 'N/A' to a number.

TO_BINARY_DOUBLE Function Syntax

The syntax for TO_BINARY_DOUBLE is:

TO_BINARY_DOUBLE(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): This is the key part. It tells Oracle what value to return (e.g., 0 or NULL) if the conversion fails.
• 'fmt' / 'nlsparam' (Optional): Format models, just like in TO_NUMBER, used when converting a string.

Oracle TO_BINARY_DOUBLE Function Examples

Here are two practical examples of how to use TO_BINARY_DOUBLE.

Example 1: Converting a NUMBER to BINARY_DOUBLE using TO_BINARY_DOUBLE

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

Query:

SELECT 
  1234.56 AS "Original_Number",
  TO_BINARY_DOUBLE(1234.56) AS "Double_Precision"
FROM DUAL;

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

Original_Number Double_Precision
--------------- ----------------
        1234.56       1.2346E+003

Example 2: Handling Conversion Errors using TO_BINARY_DOUBLE

This is the function's most powerful feature. If you try to convert an invalid string like '2oo' or 'N/A', a normal query would stop with an error. This function can return a default value instead.

Query:

SELECT 
  TO_BINARY_DOUBLE('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