Oracle decode() function

#Database, #Oracle

Decode function has the same functionality as an IF-THEN-ELSE statement. It is present in Oracle 9i and above. It has the syntax

decode(column/expression, value, substitute, default value)

For example,

decode(gender, 'M', 'Male', 'F', 'Female', gender) gndr

This is like saying the following in PL/SQL:

IF gender = 'M' THEN
  gndr := 'Male';
ELSIF gender = 'F' THEN
  gndr := 'Female';
ELSE
  gndr := gender;
END IF;

An SQL example,

select name, decode(gender, 'M', 'Male', 'F', 'Female', gender) gndr from employee;

Decode can have a maximum of 255 comma separated components. I you have so many components, then you might need to rethink the SQL you are writing.