Oracle DECODE function

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.

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated. View all posts by sqlandplsql