NVL

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

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

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

• nvl2
• Decode
• Coalesce
• Nullif

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	#