Oracle SQL Decode Function

The SQL Decode function offers a way to to write expressions. In addition, sometimes you want to write complex programming logic. The problem is that SQL does not provide this logic, as SQL is a command based language. This is where vendor specific functions such as the Oracle Decode come into play.

Functions in SQL create links between the SQL programming language and the database programming environment, allowing for multi-line constructs. The Decode function is one such example. This function acts like an If..Else...End..If construct with some variation. The syntax for the Decode function is:

DECODE(col/expr, search1, result1[, search2, result2 ]...[, default ])

Decode compares each column/expression for the search value one by one. If the column/expression matches the search value, then the result1 is returned. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The example below decodes the value region_id. If region_id is 1, then the function returns 'Northeast'; if region_id is 2, then it returns 'South'; and so forth. If region_id is not 1, 2, 3, or 4, then the function returns 'Other'.

SELECT employees_id,last_name,first_name,
DECODE (region_id, 1, 'Northeast',
                                  2, 'South',
                                  3, 'Midwest',
                                  4, 'West',
                                   'Other')
as "Region" FROM employees;

You can test for many more search values if needed. The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.