The NVL Function in SQL

When you query a table, the null values stored in a column (e.g commission_pct) are shown as (null). Using the NVL function, you can replace these nulls with some meaningful values. According to the following syntax, if expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.  In the following example, you get a list of employees along with their commissions, replacing null values with zero if the employees are not entitled to get commissions.

NOTE

In the presented example we used a number column and replaced all null values with zero. Null values in a character column must be replaced as shown in the following example, where a character value (‘None’) is used to replace all null values in the state province column.

SELECT city,NVL(state_province, 'None') Province FROM locations;

    Syntax :

NVL (expr1, expr2)

    SQL Statement :

SELECT    last_name, NVL(commission_pct, 0) commission

FROM      employees

WHERE    last_name LIKE 'B%'

ORDER    BY last_name;

    Output  :