Some DBMSs have an NVL2() function that allows us to replace a value with another value, the new value being determined by whether or not the initial value is null.

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

In contrast, with the NVL() function, some DBMSs accept only two arguments (which makes the function a synonym for the IFNULL() function) while others accept an unlimited number of arguments (which makes it a synonym for the COALESCE() function).

Syntax

The syntax for NVL2() 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, 'Beer', 'Wine');

Result:

Wine

Here, 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('Beach', 'Beer', 'Wine');

Result:

Beer

When the Replacement Values are Null

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

Example:

SELECT 
  NVL2('Beach', null, 'Wine') AS r1,
  NVL2(null, 'Beer', null) AS r2;

Result:

r1	r2
------- ----
NULL	NULL

DBMSs that support the NVL2() function include Oracle, MariaDB, Exasol, Vertica, Snowflake, and IBM DB2. Amazon Redshift implements NVL2() as an expression.