Oracle/PLSQL: NANVL Function

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.

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 10g, Oracle 11g

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.