# NVL2 function in Oracle

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’.