Nanvl – Oracle SQL Function

NANVL is used to return an alternate value for a BINARY_FLOAT or BINARY_NUMBER that has a Nan (Not a Number) value.

The number to check is the first argument, and the second argument is the replacement value if the 1st argument is Nan.

The best use f this function is to convert Nan values into NULLs or 0.

The example is using the Oracle function to_binary_float to create a Nan value as a demo, but the NANVL function would usually be used on a table column in a SELECT.

Example:

SELECT NANVL(to_binary_float(‘Nan’),0) as nanvl_value from dual;

NANVL_VALUE
———————–
0