SQL NVL2 Function
SQL NVL2 function is similar to NVL except that it has 3 attributes and it never returns value of first attribute, instead depending on whether first attribute is null or not, SQL NVL2 function always returns value of second or third attribute. The syntax is:
NVL2 (string_to_be_tested, string_if_not_null, string_if_null);
SQL NVL2 function can be best described in form of IF statement:
IF string_to_be_tested IS NOT NULL
THEN string_if_not_null
ELSE string_if_null
END IF;
SQL NVL2 function returns one of two values, depending on whether the first attribute is null or not:
Example:
The following query uses SQL NVL2 function to check if date of birth is entered or not:
SELECT full_name, NVL2(TO_CHAR( hire_date), ‘Entered’,’Not Entered’) FROM employee;
Output:
Note: TO_CHAR is used to convert date value to string.
Example 2:
The following statement uses SQL NVL2 function to tell if the manager exists or not for each employee in employee table:
SELECT full_name, NVL2(TO_CHAR(mgr_id), ‘Manager Exists’, ‘Manager does not exist') FROM employee;
Output:
