DECODE – Function – Oracle

The word decode triggers our mind to think of a mysterious task. But, in this context, it is a function in DBMS Oracle to perform a search task.

Functional usage:

DECODE compares expression to each search value one by one.

If expression is equal to a search, then it returns the corresponding result.

If no match is found, then it returns the assigned default value.

If default is omitted, then it returns null.

In a simple words to remember, this function is a simplified form of nested IF-Then-Else or a expression based Case statement.

Syntax:

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

Example:

This example is to give you a basic understanding on the function decode in the SQL statement. This example has hard coded values to show directions. But, you can also use this function in PL/SQL program.

SELECT 
       DECODE (3, 1, 'East', 
                  2, 'West', 
                  3, 'North', 
                  4, 'South',
                     'You are here') "Direction" 
  FROM Dual;

Thanks for reading. You can write the comment and share the usage of this function in your code.