NVL2 is an advanced function that the Oracle database supports. It is used to substitute a value, if a NULL value is encountered and to substitute another value, if a non-NULL value is encountered. The NVL2 function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax:
NVL2 (value, replace_with_if_not_null, replace_with_if_null)
Parameters:
value: It is used to specify the value to be tested for a NULL value.
replace_with_if_not_null: It is used to specify the value to return if value is not NULL.
replace_with_if_null: It is used to specify the value to return if value is NULL.
Example:
SELECT NVL2 (attendance, 'Present', 'Absent') FROM students; |
Explanation:
Here, the NVL2 function will return ‘Absent’ if the attendance field contains a NULL value. Otherwise, it will return ‘Present’.