NVL

The NVL function replaces a null value with a second value.

SQL syntax

NVL(Expression1, Expression2)

Parameters

NVL has the parameters:

Parameter	Description
`Expression1`	The expression whose values are to be tested for NULL.
`Expression2`	The alternate value to use if the value of `Expression1` is NULL.

Description

The data types of Expression1 and Expression2 must be compatible.
If Expression1 is NULL, the NVL function returns Expression2. If Expression1 is NOT NULL, the NVL function returns Expression1.
The NVL function can be used in the WHERE or HAVING clause of SELECT, UPDATE, or DELETE statements and in the SELECT list of a SELECT statement.

Examples

This example checks for null values of commission_pct and replaces them with 'Not Applicable' for employees whose last name start with B.

Command> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
       > FROM employees 
       > WHERE last_name LIKE 'B%'
       > ORDER BY last_name;
< Baer, Not Applicable >
< Baida, Not Applicable >
< Banda, .1 >
< Bates, .15 >
< Bell, Not Applicable >
< Bernstein, .25 >
< Bissot, Not Applicable >
< Bloom, .2 >
< Bull, Not Applicable >
9 rows found.