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!