NVL(expr1, expr2)

with the result being expr1 if expr1 is not null, or expr2 if expr1 is null. But how about this function:

NVL2(expr1, expr2, expr3)

The NVL2 function provides a yes / no type response to whether a value is null or not. If expr1 is not null, then expr2 is returned. If expr1 is null, then expr3 is returned. For example

SELECT NVL2(1, 2, 3) FROM DUAL;

NVL2(1,2,3)
-----------
2

The value of the first expression is not null, therefore the value of the second expression is returned. In the next example, the first expression is null, so the value of the third is returned.

SELECT NVL2(NULL, 2, 3) FROM DUAL;

NVL2(NULL,2,3)
--------------
3

This function is very useful in the situation where all that is needed is an indication of whether a value exists or not.

NVL2 is documented as a new feature in Oracle8i. So I found the following (note the database version) interesting today:

SQL*Plus: Release 9.2.0.7.0 - Production on Tue May 9 10:00:26 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle8 Release 8.0.5.2.1 - Production
PL/SQL Release 8.0.5.2.0 - Production

SQL > select nvl2(1, 2, 3) from dual;

NVL2(1,2,3)
-----------
2

Note: This tip was tested using Oracle9i and Oracle 8.0.5.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.