In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.
The syntax for the NVL function is:
NVL( string1, replace_with )
string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.
Applies To:
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
Example #1:
select NVL(supplier_city, 'n/a')
from suppliers;
The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.
Example #2:
select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;
This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.
Example #3:
select NVL(commission, 0)
from sales;
This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.