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!