Compares the value of the first argument expression with each search_expression and, if a match is found, returns the corresponding match_expression. If no match is found, then the function returns the default_expression. If a default_expression is not specified and no match is found, then the function returns a NULL value.

Syntax

DECODE ( expression, search_expression, match_expression              
 [ , search_expression, match_expression...] 
 [ , default_expression] )

Example

This example illustrates one way to use the DECODE function:

SELECT ename, DECODE (deptno,
             10, 'ACCOUNTS',
             20, 'RESEARCH',
             30, 'SALES',
             40, 'SUPPORT',
             'NOT ASSIGNED'
          )
   FROM employee ;

Notes

  • Use a simple case expression when SQL‑compatible syntax is a requirement.
  • The first argument expression can be of any type. The types of all search_expressions must be compatible with the type of the first argument.
  • The match_expressions can be of any type. The types of all match_expressions must be compatible with the type of the first match_expression.
  • The type of the default_expression must be compatible with the type of the first match_expression.
  • The type of the result is the same as that of the first match_expression.
  • If the first argument expression is NULL, then the value of the default_expression is returned, if it is specified. Otherwise NULL is returned.

Compatibility

Progress extension