What is DECODE function used for?

DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution. For every value that is given in the DECODE function it makes an if then check and matches the value. The general format of DECODE function is given below:

DECODE(value,if1,then1,if2,then2,……,else)

In the above the else can be a function or a literal or another column.

Let us see this with an example

Consider a table named as exforsys which has the following attributes namely:Empname, sex, salary

Select * from exforsys;

Gives output as

Empname     sex   salary ------------ ---- ------- Priya         F     20000 Sri           F     10000 Sam           M     20000 Raj           M     20000 Sita          F     10000

In the above if we give the DECODE function as below namely:

Select empname, sex, DECODE(salary,’20000’,’50000’,’70000’) from exforsys;

The output of above DECODE function would result as below:

Empname     sex     salary ------------ ----   ------- Priya          F     50000 Sri            F     70000 Sam            M     50000 Raj            M     50000 Sita           F     70000