meta data for this page
  •  

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