Oracle/PLSQL: NVL2 Function
Learn how to use the Oracle/PLSQL NVL2 function with syntax and examples.
The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
The syntax for the Oracle/PLSQL NVL2 function is:
NVL2( string1, value_if_NOT_null, value_if_null )
Parameters or Arguments
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.
The NVL2 function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Let's look at some Oracle NVL2 function examples and explore how you would use the NVL2 function in Oracle/PLSQL.
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;
The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.
Another example using the NVL2 function in Oracle/PLSQL is:
select supplier_id, NVL2(supplier_desc, supplier_name, supplier_name2) from suppliers;
This SQL statement would return the supplier_name2 field if the supplier_desc contained a null value. Otherwise, it would return the supplier_name field.