DECODE

The DECODE function compares an expression to each search value one by one. If the expression is equal to the search value, then the result value is returned. If no match is found, then the default value (if specified) is returned. Otherwise NULL is returned.

SQL syntax

DECODE(Expression, {SearchValue, Result [,...])} [,Default])

Parameters

DECODE has the parameters:

Parameter	Description
`Expression`	The expression that is compared to the search value.
`SearchValue`	An expression is compared to one or more search values.
`Result`	If the expression is equal to a SearchValue, then the specified Result value is returned.
`Default`	If no match is found, the default value is returned. Default is optional. If `Default` is not specified and no match is found, then NULL is returned.

Description

If an expression is NULL, then the NULL expression equals a NULL search value.

Examples

The following example invokes the DECODE function. In the locations table, if the column country_id is equal to 'IT', then the function returns 'Italy.' If the country_id is equal to 'JP', then the function returns 'Japan.' If the country_id is equal to 'US,' then 'United States' is returned. If the country_id is not equal to 'IT' or 'JP' or 'US,' then the function returns 'Other.'

Command> SELECT location_id,
>        DECODE (country_id, 'IT', 'Italy',
>                            'JP', 'Japan',
>                            'US', 'United States',
>                                  'Other')
> FROM locations WHERE location_id < 2000;
LOCATION_ID, EXP
< 1000, Italy >
< 1100, Italy >
< 1200, Japan >
< 1300, Japan >
< 1400, United States >
< 1500, United States >
< 1600, United States >
< 1700, United States >
< 1800, Other >
< 1900, Other >
10 rows found.