Oracle NVL2 function

NVL2 function extends the funcitonality of NVL function. NVL function prints the value of the variable unless a null value is encountered; in which case a value is substituted for the null. In addition to this, NVL2 also allows you to substitute a value if the value of the variable is not null. Syntax:

NVL2(variable, not_null_substitute, null_substitute)


select id, name, NVL2(phone, 'Provided', 'Not Provided') from staff_listing;

Here the NVL2 function prints Provided if a phone number is provided and Not Provided in the value is null.

NVL2 function maintains the same functionality in Oracle 8i, 9i, 10g, 11g, and it is expected to remain the same in future versions.