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.