In Oracle/PLSQL, the NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.
Syntax
The syntax for the NANVL function is:
NANVL( value, replace_with )
value is the BINARY_FLOAT or BINARY_NUMBER to test for a Nan (Not a number).
replace_with is the value returned if value is Nan (not a number).
Applies To
- Oracle 11g, Oracle 10g
Example #1
select NANVL(binary1, 0)
from test_table;
The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.
Example #2
select NANVL(binary1, NULL)
from test_table;
The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.