Oracle NVL() Function

Oracle NVL() is a built-in function that allows you to replace NULL values with a specified value.

Oracle `NVL()` Syntax

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

NVL(expr1, expr2)

Parameters

expr1: Required. A value or expression.
expr2: Required. Another value or expression.

Both expr1 and expr2 can have any data type. If their data types are different, the Oracle database implicitly converts one data type to another. If they cannot be implicitly converted, the database returns an error. The implicit conversions are implemented as follows:

If expr1 is character data, then the Oracle database converts expr2 to the data type of expr1 before comparison and returns VARCHAR2 in the character set of expr1.
If expr1 is numeric, then the Oracle database determines which parameter has the highest numeric precedence, implicitly converts the other parameter to that data type, and returns that data type.

Return Value

If expr1 is NULL, then Oracle NVL returns expr2. If expr1 is not NULL, then NVL returns expr1.

If both parameters are NULL, NVL() returns NULL.

Oracle `NVL()` Examples

Here are several examples that demonstrate the usage of the Oracle NVL() function.

Basic Usage

SELECT
    NVL(NULL, 'expr1 is null'),
    NVL('expr1 is not null', 'expr2')
FROM dual;

Output:

NVL(NULL,'EXPR1ISNULL')    NVL('EXPR1ISNOTNULL','EXPR2')
__________________________ ________________________________
expr1 is null              expr1 is not null

NULL Parameters

If both parameters are NULL, NVL() returns NULL.

SET NULL 'NULL';
SELECT
    NVL(NULL, NULL)
FROM dual;

Output:

NVL(NULL,NULL)
_________________
NULL

In this example, we use the SET NULL 'NULL'; statement to display the NULL value as the string 'NULL'.

Conclusion