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.

Advertisements

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.

3 thoughts on “Oracle DECODE function”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s