NVL

From Oracle FAQ
Jump to: navigation, search

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.

Syntax[edit]

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'.

Examples[edit]

Example #1

SELECT nvl(salary, 'Sorry, no pay!') FROM employees;

Display "Sorry, no pay!" whenever an employee's salary is NULL.

Example #2

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 #3

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 #4

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.

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #