NVL function

NVL function can be used to substitute a value when null in encountered. It has the following syntax:

NVL(string, replacement_string)

If string has a value, it will be displayed. If the string is null, the replacement string will be displayed.

select NVL(salary, 'unpaid volunteer') from staff

This query prints staff salaries. Volunteers do not have a salary so the value for their salary is null. In the query, when the salary is null, replacement string ‘unpaid volunteer’ is printed.

select NVL(salary, monthly_invoice) from worker

A paid worker can be an employee or a contractor. An employee receives a monthly salary and a contractor invoices his hours of work at the end of the month. In this query, if the salary is not null, the salary is printed. If the salary is null, the monthly_invoice is printed.

select distinct id, NVL((select distinct 'Y' from table1 where x = y), 'N') active where c = d      

NVL function can also be used in a subquery as in the example above.

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)

Example

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.

By master