Compares values and if they match, returns a corresponding value.
DECODE(expr, srch, result [, srch, result ]... [, default ])
DECODE compares values of the value expression to be converted and the search values one by one. If the values match, a corresponding result value is returned. If no values match, the default value is returned if it has been specified. A NULL value is returned if a default value has not been specified.
If the same search value is specified more than once, then the result value returned is the one listed for the first occurrence of the search value.
The following data types can be used in result values and in the default value:
CHAR
VARCHAR
NCHAR
NCHAR VARYING
TEXT
INTEGER
BIGINT
NUMERIC
DATE
TIME WITHOUT TIME ZONE
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE
The same data type must be specified for the values to be converted and the search values. However, note that different data types may also be specified if a literal is specified in the search value, and the value expression to be converted contains data types that can be converted. When specifying literals, refer to "Table A.1 Data type combinations that contain literals and can be converted implicitly" in "A.3 Implicit Data Type Conversions" for information on the data types that can be specified.
If the result values and default value are all literals, the data types for these values will be as shown below:
If all values are string literals, all will become character types.
If there is one or more numeric literal, all will become numeric types.
If there is one or more literal cast to the datetime/time types, all will become datetime/time types.
If the result values and default value contain a mixture of literals and non-literals, the literals will be converted to the data types of the non-literals. When specifying literals, refer to "Table A.1 Data type combinations that contain literals and can be converted implicitly" in "A.3 Implicit Data Type Conversions" for information on the data types that can be converted.
The same data type must be specified for all result values and for the default value. However, different data types can be specified if the data type of any of the result values or default value can be converted - these data types are listed below:
Other result values or default value | ||||
|---|---|---|---|---|
Numeric type | Character type | Date/time type | ||
Result value (any) | Numeric type | Y | N | N |
Character type | N | Y | N | |
Date/time type | N | N | S (*1) | |
Y: Can be converted
S: Some data types can be converted
N: Cannot be converted
*1: The data types that can be converted for date/time types are listed below:
Other result values or default value | |||||
|---|---|---|---|---|---|
DATE | TIME WITHOUT TIME ZONE | TIMESTAMP WITHOUT TIME ZONE | TIMESTAMP WITH TIME ZONE | ||
Result value (any) | DATE | Y | N | Y | Y |
TIME WITHOUT TIME ZONE | N | Y | N | N | |
TIMESTAMP WITHOUT TIME ZONE | Y | N | Y | Y | |
TIMESTAMP WITH TIME ZONE | Y | N | Y | Y | |
Y: Can be converted
N: Cannot be converted
The data type of the return value will be the data type within the result or default value that is longest and has the highest precision.
Example
In the following example, the value of col3 in table t1 is compared and converted to a different value. If the col3 value matches search value 1, the result value returned is "one". If the col3 value does not match any of search values 1, 2, or 3, the default value "other number" is returned.
SELECT col1, DECODE(col3, 1000, 'one',
2000, 'two',
3000, 'three',
'other number') "num-word"
FROM t1;
col1 | num-word
------+----------
1001 | one
1002 | two
1003 | three
(3 rows)