NANVL function in Oracle

NANVL is an advanced function that the Oracle database supports. It is used to substitute a value for a floating point number, when a Nan (Not a number) value is encountered. The NANVL function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, and Oracle 10g.

Syntax:

NANVL (value, replace_with)


Parameters:
value: It is used to specify the value to be tested for Nan.
replace_with: It is used to specify the value to return if value is Nan.

Example:

select NANVL(marks, 0) from students;

Explanation:
Here, the NANVL function will return 0 if the marks field contain a Nan value. Otherwise, the value of the ‘marks’ field will be returned.