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

Syntax

The syntax goes like this:

NVL(expr1, expr2)

If expr1 is null, then expr2 is returned. Otherwise expr1 is returned.

Example

Here’s an example to demonstrate:

SELECT NVL(null, 'Fish')
FROM DUAL;

Result:

Fish

And here’s what happens when the first argument is a non-null value:

SELECT NVL('Brocoli', 'Fish')
FROM DUAL;

Result:

Brocoli

Invalid Argument Count

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

SELECT NVL()
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 NVL(1, 2, 3)
FROM DUAL;

Result:

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

The NVL2() Function

You can alternatively use the NVL2() function, which allows you to specify a different value to return in the event the first argument is not null.