DECODE()

Available in: DSQL, PSQL

Added in: 2.1

Description: DECODE is a shortcut for the so-called “simple CASE” construct, in which a given expression is compared to a number of other expressions until a match is found. The result is determined by the value listed after the matching expression. If no match is found, the default result is returned, if present. Otherwise, NULL is returned.

Result type: Varies

Syntax:

DECODE ( <test-expr>,
         <expr>, result
         [, <expr>, result ...]
         [, defaultresult] )
The equivalent CASE construct:
CASE <test-expr>
   WHEN <expr> THEN result
   [WHEN <expr> THEN result ...]
   [ELSE defaultresult]
END
Caution

Matching is done with the “=” operator, so if <test-expr> is NULL, it won't match any of the <expr>s, not even those that are NULL.

Example:

select name,
       age,
       decode( upper(sex),
               'M', 'Male',
               'F', 'Female',
               'Unknown' ),
       religion
from people