DECODE function in Oracle

by

DECODE is an advanced function that the Oracle database supports. It is used to work as an IF-THEN-ELSE statement. The DECODE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.

Syntax: 

DECODE( expression, search, result, search, result... , default )

Parameters:
expression: It is used to specify the value to be compared.
search: It is used to specify the value to be compared against expression.
result: It is used to specify the value to return, if expression is equal to search.
default: It is used to specify the default value to be returned if no matches are found.

Example: 

SELECT name,
DECODE ( student_id, 1, 'Tom', 2, 'Mike', 3, 'Harry',
                    'Jim') result
FROM students;

Explanation:
Here, each student_id value will be compared one by one by the DEFAULT function. The default value ( ‘Jim’ ) will be returned if no matches are found.