PL/SQL NVL2

The NVL2 function returns the value returned by a query based, if the expression is null or not null.
If expression1 is not null, then NVL2 returns expression2. If expression1 is null, then NVL2 returns expression3.

NVL2 syntax

NVL2( expression1, expression2, expression3 ) 

NVL2 example

 
select NVL2( null, 1, 2 ) from dual;

Result: 2


select NVL2( 'value not null', 1, 2 ) from dual;

Result: 1


select NVL2( 12345, 'OK', 'NOT OK' ) from dual;

Result: OK

NVL2 example

STUDENT_IDFIRST_NAMELAST_NAMECITYADDRESS
1DanielSCOTTNew York
2AnthonySIMMONSChicago
3SophiaTHOMPSONLos Angeles
select s.STUDENT_ID, s.FIRST_NAME, s.LAST_NAME, 
s.CITY, NVL2(s.ADDRESS, s.ADDRESS, s.CITY) AS STUDENT_ADDRESS
from students s;
STUDENT_IDFIRST_NAMELAST_NAMECITYSTUDENT_ADDRESS
1DanielSCOTTNew YorkNew York
2AnthonySIMMONSChicagoChicago
3SophiaTHOMPSONLos AngelesLos Angeles