Replace NULL Values Using NVL Function in SQL

In the previous example, you saw that null values in the calculated commission column displayed (null) for employees other than sales representatives, which is not considered a good presentation of data. To override this default value with some acceptable text, you are provided with NVL function. Before we see a practical example, let’s first acquaint ourselves with its syntax: 

    Syntax :

NVL(expression1, expression 2)

According to the syntax, you put source column or expression containing null in expression1 position, and the target value that you wish to see instead of null is placed in expression2. NVL function can be used to convert any data type. One thing that you have to take care of is the use of expression2 which must be of same data type as that of expression1. For example, if you’re converting a numeric column, then you must use a number in expression2 position. Repeating the previous example, you’ll add the NVL function to convert null values – in COMMISSION_PCT (a table column) and COMMISSION (an arithmetic expression) – to zero in the following SQL statement.

    SQL Statement : 

SELECT  last_name, job_id, salary,

                nvl(commission_pct,0) "Percent",  

                salary * nvl(commission_pct, 0)/100 "Commission"

FROM     employees;

    Output :