Skip to content

NVL2

Return a specific value when an expression evaluates to not null and a specific value when the same expression evaluates to null.

NVL2( expression, not_null_return_value, null_return_value )

The second and third arguments to the function must have compatible data types.

For example, when the city column is not null, return the city name, and when it is null, return No City:

premdb=# select teamid, nvl2(city, city, 'No City') from team order by 1 desc;
 teamid |     city      
--------+---------------
    50 | No City
    49 | No City
    48 | No City
    47 | Wolverhampton
    46 | London
    45 | Wigan
    44 | London
...

You can use the DECODE function to produce the same results. For example:

premdb=# select teamid, decode(city, null, 'No City', city) from team order by 1 desc;
 teamid |     city      
--------+---------------
    50 | No City
    49 | No City
    48 | No City
    47 | Wolverhampton
    46 | London
    45 | Wigan
    44 | London
...

Parent topic:Conditional Expressions