nvl is an Oracle SQL function that will return a non-NULL value if a NULL value is passed to it. A substituted value will be returned for each NULL value encountered.
NVL( string/number, replace_with )
- string/number is the string or number to test for a NULL value.
- replace_with is the value returned if string/number is NULL.
NVL(a,b) == if 'a' is NULL then return 'b' else return 'a'.
SELECT nvl(salary, 'Sorry, no pay!') FROM employees;
Display "Sorry, no pay!" whenever an employee's salary is NULL.
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.
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.
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.
|Glossary of Terms|