Oracle DECODE function

DECODE function implements IF-ELSE statements in SQL.

It accepts only scalar values.

Syntax:

DECODE(expression,search1, result1, search2, result2,…., searchN, resultN, default)

Maximum 255 components can be included in decode function.

Examples:-

Consider below table – direction. It stores direction as abbreviation.

ID          DIRECTION
———- ——————–
1               N
2               S
3               E
4            W

Write a query to display E for EAST, W for WEST etc.

select id,decode(direction,’N’,’NORTH’,’S’,’SOUTH’,’E’,’EAST’,’W’,’WEST’,null) as direction from direction;

ID           DIRECTION
———- —————-
1             NORTH
2             SOUTH
3             EAST
4             WEST

In procedural way above logic can be written as

If DIRECTION = ‘N’ then

Value := ‘NORTH’;

elsif DIRECTION = ‘S’ then

Value := ‘SOUTH’;

elsif DIRECTION = ‘W’ then

Value := ‘WEST’;

elsif DIRECTION = ‘E’ then

Value := ‘EAST’;

else

value := null:

end if;

Nested decode function also allowed.

select id,decode(direction,’N’,’NORTH’,’S’,decode(‘SOUTH’,’SOUTH’,’SOUTH-WEST’,null),’E’,’EAST’,’W’,’WEST’,null) as direction from direction;

ID           DIRECTION
———- —————-
1             NORTH
2             SOUTH-WEST
3             EAST
4            WEST

If it is S it converts S to SOUTH and SOUTH to SOUTH-WEST.

Decode function is one of the most widely used function in Oracle SQL.

Note : – All examples tested in Oracle 11g Release 2.