Home > SQL, Technical > NVL,DECODE,CASE,NULLIF functions in Oracle

NVL,DECODE,CASE,NULLIF functions in Oracle

July 26, 2012

Function 1: NVL

Purpose : Returns second parameter if the first param is null

Syntax: Nvl(param1,param2)

Example : SELECT Nvl(null,’karu’) FROM DUAL;

Output:  karu

Example : SELECT Nvl(null,null) FROM DUAL;

Output:  returns null

———————————————————————————————

Function 2: DECODE

Purpose : Decode a given string1 to string2

Syntax: DECODE(str1, str1,str2….list of strings)

Example : SELECT DECODE(‘TN’, ‘TN’,’TAMIL NADU’,’AN’,’ANDRA’,’KA’,’KARNATAKA’) FROM DUAL;

Output:  TAMIL NADU

Example : SELECT DECODE(‘NN’, ‘TN’,’TAMIL NADU’,’AN’,’ANDRA’,’KA’,’KARNATAKA’,’Others’) FROM DUAL;

Output:  returns null

———————————————————————————————

Function 3: CASE

Purpose : like switch case in programming languages

Syntax: case ‘value’ when ‘value1’  then ‘do actions1’ when ‘value2’ then ‘do actions2’ end

Example : SELECT CASE ‘TN’

WHEN ‘TN’ THEN ‘TAMIL NADU’

WHEN ‘AN’ THEN ‘ANDRA’

WHEN ‘KA’ THEN ‘KARNATAKA’

END AS state

FROM DUAL;

Output:  TAMIL NADU

Example : SELECT

CASE ‘NN’

WHEN ‘TN’ THEN ‘TAMIL NADU’

WHEN ‘AN’ THEN ‘ANDRA’

WHEN ‘KA’ THEN ‘KARNATAKA’

END AS state

FROM DUAL;

Output:  returns null

———————————————————————————————

Function 4:NULLIF

Purpose : If param1 and param2 is same then returns null otherwise param1

Syntax: NULLIF(param1,param2)

Example : SELECT NULLIF(‘karu’,’karu’) FROM DUAL;

Output:  Returns null

Example : SELECT NULLIF(‘karuvachi’,’karu’) FROM DUAL;

Output:  karuvachi

Advertisements
Categories: SQL, Technical
%d bloggers like this: