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.

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.