Null related functions

NVL

  • If null found then replace it with other value
  • It considers ” as null
select NVL('A','B') as result from dual; --A
select NVL(null,'B') as result from dual; --B

NVL2

  • Opposite of NVL — If 1st argument null then return 3rd argument else return 2nd argument
  • It considers ” as null
select NVL2('A','B','C') as result from dual; --B
select NVL2(null,'B','C') as result from dual; --C
select NVL2('A',null,'C') as result from dual;--null
select NVL2(null,'B',null) as result from dual;--null

NULLIF

  • If both arguments are same return null
  • If both arguments are not same return the 1st valye
select NULLIF('A','A') as result from dual;--If both are same return null
select NULLIF('A','B') as result from dual;--If both are not same return 1st value
select NULLIF(null,'A') as result from dual;--Exception but '' instead of  null then (null) is the output
select NULLIF('A',null) as result from dual;--A

COALESCE

  • Finds the first not null expression
  • If all the arguments are null then result is null
  • Considers ” as null
select coalesce ('A','B','C','D','E') as result from dual;--A
select coalesce (null,'B','C','D','E') as result from dual;--B
select coalesce (null,'','C','D','E') as result from dual;--C
select coalesce (null,'',null,'D','E') as result from dual;--D
select coalesce (null,'',null,'','E') as result from dual;--E
select coalesce (null,'',null,'',null) as result from dual;--null
select coalesce (null,'',null,'','') as result from dual;--null

Leave a Comment