syntax and example of nvl2 function in oracle sql

The NVL2 function in Oracle SQL / PLSQL enhances the functionality of the NVL function as NVL2 allows us to substitute a value when a NULL is encountered and also when a NULL is not encountered.

The Syntax for the NVL2 function in Oracle SQL / PLSQL is:

SELECT NVL2(string1, replace_with_when_not_null, replace_with_when_null)
FROM table_name;

• ‘string1’ is the field or column for testing NULL value
• ‘replace_with_when_not_null’ is the string with which the NOT NULL values will be substituted with.
• ‘replace_with_when_null’ is the string with which the NULL values will be substituted with.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000
104	Emp D	30000
105	Emp E	32000

Example 1:

Using NVL2 with numbers and string

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL2(department,'Department','No Department')
       ,NVL2(commission,20,0)
FROM employee;

We will get the following output:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Department	20
102	Emp B	20000	Department	20
103	Emp C	28000	No Department	20
104	Emp D	30000	No Department	20
105	Emp E	32000	No Department	20

Here we can see that we have substituted a string and number type in ‘department’ and ‘commission’ columns respectively using the NVL2 function.

Example 2:

Using NVL2 to get the values in records or rows.

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000
104	Emp D	30000
105	Emp E	32000

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL2(department,department,'No Department')
       ,NVL2(commission,commission,0)
FROM employee;

We will get the following output:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	No Department	0
104	Emp D	30000	No Department	0
105	Emp E	32000	No Department	0

Here we can see that we have retrieved the department names and commission details of the employees as present in the data base and we have also substituted the NULL values in ‘department’ and ‘commission’ columns as ‘No Department’ and ‘0’ wherever NULL was present.

In this case NVL2 is behaving as NVL