In Oracle Database, the NVL2() function allows us to replace null values with another value.

It’s similar to the NVL() function, except that it accepts three arguments instead of two. This allows us to specify a different value to return in the event the first argument is not null.

Syntax

The syntax goes like this:

NVL2(expr1, expr2, expr3)

If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

Example

Here’s an example to demonstrate:

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

Result:

3

The first argument was null and so the third argument was returned.

And here’s what happens when the first argument is not null:

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

Result:

2

When the Replacement Values are Null

If the second or third argument is null, then you could end up with a null result.

Example:

SET NULL '(null)';
SELECT 
    NVL2(1, null, 3) AS "r1",
    NVL2(null, 2, null) AS "r2"
FROM DUAL;

Result:

       r1        r2 
_________ _________ 
   (null)    (null)

Regarding the first line SET NULL '(null)';, I added this line so that my SQLcl session returns that value whenever the result is null.

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned (like I’ve done here).

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT NVL2()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

And passing too many arguments also causes an error:

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

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"