Oracle NVL2() Function

Oracle NVL2() is a built-in function that allows you to determine which value to return based on whether an expression is null.

Oracle NVL2() Syntax

Here is the syntax for the Oracle NVL2() function:

NVL2(expr1, expr2, expr3)

Parameters

expr1

Required. A value or expression.

expr2

Required. A value or expression.

expr3

Required. A value or expression.

Parameter expr1 can have any data type. Parameters expr2 and expr3 can have any data type except LONG.

If the data types of expr2 and expr3 are different, the Oracle database will implicitly convert one to the other. If they cannot be implicitly converted, the database returns an error. If expr2 is a character or numeric data type, implicit conversion is implemented as follows:

  • If expr2 is character data, the Oracle database converts expr3 to the data type of expr2 before returning, unless expr3 is a null constant. In that case, no data type conversion is necessary, and the database returns VARCHAR2 in the character set of expr2.
  • If expr2 is numeric data, the Oracle database determines which parameter has the highest numeric priority, implicitly converts the other parameter to that data type, and then returns that data type.

Return Value

If expr1 is not NULL, the Oracle NVL2(expr1, expr2, expr3) function returns expr2; otherwise, it returns expr3.

If any parameter is NULL, NVL2() returns NULL.

Oracle NVL2() Example

SELECT
    NVL2(1, 'A', 'B'),
    NVL2(NULL, 'A', 'B')
FROM dual;

Output:

NVL2(1,'A','B')    NVL2(NULL,'A','B')
__________________ _____________________
A                  B

In this example, NVL2(1,'A','B') returns A because the first parameter is not NULL, and NVL2(NULL,'A','B') returns B because the first parameter is NULL.

Conclusion

Oracle NVL2() is a built-in function that allows you to determine which value to return based on whether an expression is null.