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.
