Other related articles:

Recently viewed articles:

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

SQL NVL2 function returns one of two values, depending on whether the first attribute is null or not:

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;

sql nvl2 image1

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;

sql nvl2 image2