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.


NANVL (value, replace_with)

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.


select NANVL(marks, 0)
from students;

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.