DECODE

The DECODE function compares an expression to each search value one by one. If the expression is equal to the search value, 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. `Expression` can be `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data types. Both TimesTen and Oracle Database data types are supported.
`SearchValue`	An expression is compared to one or more search values.
`Result`	If the expression is equal to a `SearchValue`, 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', the function returns 'Italy'. If the country_id is equal to 'JP', the function returns 'Japan'. If the country_id is equal to 'US', 'United States' is returned. If the country_id is not equal to 'IT' or 'JP' or 'US', 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.