Oracle SQL / PLSQL NANVL function allows us to substitute a value for a floating point number such a BINARY_FLOAT or BINARY_DOUBLE, when an SQL Not A Number (NAN) value is encountered.
The Oracle SQL NANVL function is most commonly used to convert Not A Number (NAN) values to either 0 or NULL.
Oracle SQL / PLSQL Syntax for the NANVL function is:
SELECT NANVL (value_1, replace_with)
FROM table_name;
- value_1 is the BINARY_FLOAT or BONARY_NUMBER to be tested for a Not A Number (NAN)
- the replace_with is the value that will be returned if value_1 is NAN.
Example of using Oracle SQL NANVL function
SELECT NANVL(to_char(2),NULL) FROM dual;
Will return NULL.