Oracle TO_BINARY_DOUBLE and TO_BINARY_FLOAT Functions

In this article, I’ll explain what the TO_BINARY_DOUBLE and TO_BINARY_FLOAT functions do and show you some examples.

Purpose of the Oracle TO_BINARY_DOUBLE Function

The TO_BINARY_DOUBLE function converts an expression into a BINARY_DOUBLE data type.

This is a double-precision floating-point number. If you work with BINARY_DOUBLE data types (which I’ve written about here (TODO link)) then you will find this function useful.

 

Purpose of the Oracle TO_BINARY_FLOAT Function

This function converts a number to a single-precision floating point number. It’s similar to TO_BINARY_DOUBLE, but the output data type is a FLOAT.

 

Syntax

The syntax of the TO_BINARY_DOUBLE function is:

TO_BINARY_DOUBLE (expression [, format_mask [, nls_parameter] ])

The syntax of the TO_BINARY_FLOAT function is:

TO_BINARY_FLOAT (expression [, format_mask [, nls_parameter] ])

They both take the same parameters.

 

Parameters

The parameters of the TO_BINARY_DOUBLE function are:

• expression (mandatory): The expression to convert to a BINARY_DOUBLE. It can be a string, or a numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.
• format_mask (optional): This parameter specifies the format of the input value, if the input value is a character type that can be converted to a BINARY_DOUBLE.
• nls_parameter (optional): This allows you to set the NLS_PARAMETER if the input value is a character type.

The parameters of the TO_BINARY_FLOAT are the same:

• expression (mandatory): The expression to convert to a BINARY_FLOAT. It can be a string, or a numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.
• format_mask (optional): This parameter specifies the format of the input value, if the input value is a character type that can be converted to a BINARY_FLOAT.
• nls_parameter (optional): This allows you to set the NLS_PARAMETER if the input value is a character type.

 

Examples of the TO_BINARY_DOUBLE and TO_BINARY_FLOAT Functions

Here are some examples of these two function. I find that examples are the best way for me to learn about code, even with the explanation above.

For these examples, I’ll create a test table which contains numbers in different data types.

CREATE TABLE double_test (
  num_val NUMBER(8, 2),
  bin_double_val BINARY_DOUBLE,
  bin_float_val BINARY_FLOAT,
  char_val VARCHAR2(10)
);

 

INSERT INTO double_test (num_val, bin_double_val, bin_float_val, char_val)
VALUES (2468.12, 2468.12, 2468.12, ‘2468.12’);

 

Now, we can use this data in our examples.

 

Example 1: Number Value

This example uses TO_BINARY_DOUBLE and TO_BINARY_FLOAT on a NUMBER value.

SELECT num_val,
TO_BINARY_DOUBLE(num_val) AS bin_double,
TO_BINARY_FLOAT(num_val) AS bin_float
FROM double_test;

Results:

NUM_VAL	BIN_DOUBLE	BIN_FLOAT
2468.12	2468.12	2468.12

Example 2: Binary Double Value

This example shows using the TO_BINARY_DOUBLE function on a value that is already a BINARY_DOUBLE.

SELECT bin_double_val,
TO_BINARY_DOUBLE(bin_double_val) AS bin_double,
TO_BINARY_FLOAT(bin_double_val) AS bin_float
FROM double_test;

Results:

BIN_DOUBLE_VAL	BIN_DOUBLE	BIN_FLOAT
2468.12	2468.12	2468.12

 

Example 3: Binary Float Value

This example shows using this function on a value that is a BINARY_FLOAT.

SELECT bin_float_val,
TO_BINARY_DOUBLE(bin_float_val) AS bin_double,
TO_BINARY_FLOAT(bin_float_val) AS bin_float
FROM double_test;

Results:

BIN_FLOAT_VAL	BIN_DOUBLE	BIN_FLOAT
2468.12	2468.1201171875	2468.12

 

Example 4: Character Value

This example uses a number that is stored inside a character field.

SELECT char_val,
TO_BINARY_DOUBLE(char_val) AS bin_double,
TO_BINARY_FLOAT(char_val) AS bin_float
FROM double_test;

Results:

CHAR_VAL	BIN_DOUBLE	BIN_FLOAT
2468.12	2468.12	2468.12

 

Similar Functions

Some functions which are similar to the TO_BINARY_DOUBLE function are:

• TO_NUMBER: Converts a value to a NUMBER data type.
• TO_CHAR: Converts a value to a VARCHAR2 data type.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!