Oracle NVL2 function

Preamble

Oracle/PLSQL NVL2 function extends the functionality of the NVL function. It replaces the value when a Null value is encountered and also when a non-Null value is encountered.

Oracle/PLSQL syntax of NVL2 function

NVL2( string1_id, value_if_NOT_null_id, value_if_null_id )

Parameters & arguments of NVL2

• string1_id – line to check for Null value.
• value_if_NOT_null_id – this value is returned if string1 has no Null value.
• value_if_null_id – this value is returned if string1 has a Null value.

NVL2 function in the following versions of Oracle/PLSQL

Let’s consider some examples of Oracle NVL2 function and learn how to use NVL2 function in Oracle PLSQL.

SELECT NVL2(suppl_city, 'Completed', 'n/a')
FROM suppls;

The SQL query will return ‘n/a’ if the suppl_city field contains a null value. Otherwise, it will be the ‘Completed’ value.

Another example of using the NVL2 function in Oracle/PLSQL:

SELECT suppl_id,
NVL2(suppl_desc, suppl_name, suppl_name2)
FROM suppls;

This SQL query will return suppl_name2 if suppl_desc contains a Null value. Otherwise, it will return the suppl_name field.

NVL2 FUNCTION IN ORACLE SQL WITH EXAMPLE

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​