NVL2 Function in Oracle SQL – PLSQL

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


Tagged , , , , , , , , , , , . Bookmark the permalink.