DECODE

This function compares the expr with each search[1...N] in the order specified, replaces expr with the corresponding result[1...N] if a match is found, and returns the resulting value.

Syntax

DECODE(expr, search1, result1 [, search2, result2..., searchN, resultN][, default])

Arguments

expr: The value or expression to be compared.

---

search[1...N]: The search expressions.

---

result[1...N]: The return values or expressions corresponding to search[1...N].

---

default (optional): If none of the search[1...N] expressions match expr, the default value default is returned.

Usage Notes

• When expr and search are strings, they are compared using nonpadded comparison semantics. result must be of the STRING data type.
• If the first expr / search pair is numeric:
  • All search result expressions are compared with the first expression.
  • The argument with the highest numeric precedence is determined.
  • The remaining arguments are implicitly converted to this data type.
  • The return value is returned with this data type.
• Each search is evaluated before being compared to expr. Consequently, no further search is evaluated once a previous search matches expr.
• expr and each search are automatically converted to the data type of the first search before comparison.
• The maximum number of components, including expr, search, result and default, is 255.

Example 1

select col1, decode(col1, 
  1, '1.', 
  2, '2.', 
  NULL, 'IS NULL',
  'default value'
) AS col1_decoded 
from 
( select 1 as col1 
  union all select 2 as col1 
  union all select NULL as col1 
  union all select 4 as col1);

+---------+-----------------+
| COL1    | COL1_DECODED    |
+---------+-----------------+
| 1       | 1.              |
| 2       | 2.              |
| NULL    | IS NULL         |
| 4       | default value   |
+---------+-----------------+

Example 2

Without the default value "default value", NULL is returned if no search[1...N] and result[1...N] is defined for expr.

select col1, decode(col1, 
  1, '1.', 
  2, '2.', 
  NULL, 'IS NULL'
) AS col1_decoded 
from 
( select 1 as col1 
  union all select 2 as col1 
  union all select NULL as col1 
  union all select 4 as col1);

+---------+-----------------+
| COL1    | COL1_DECODED    |
+---------+-----------------+
| 1       | 1.              |
| 2       | 2.              |
| NULL    | IS NULL         |
| 4       | NULL            |
+---------+-----------------+

Example 3

select col1, decode(col1, 
 'a', 'a without spaces', 
 ' a ', 'a with spaces', 
 'ab ', 'ab right space', 
 ' ab', 'ab left space', 
 'not found'
) AS col1_decoded, 
 length(col1)
from 
( select 'a' as col1 
  union all select ' a ' as col1 
  union all select 'ab' as col1 
  union all select ' ab' as col1);

+-------+-------------------+-------------------+
| COL1  |   COL1_DECODED    |    LEGNTH(COL1)   |
+-------+-------------------+-------------------+
|  a    |  a without spaces |                 1 |
|   a   |  a with spaces    |                 3 |
|  ab   |  not found        |                 2 |
|   ab  |  ab left space    |                 3 |
+-------+-------------------+-------------------+

Example 4

Implicit Data Type Conversion

select col1, decode(col1, 
 '1', 'first', 
 '2', 'second', 
 '3 ', 'third', 
 '4 ', 'fourth', 
 'not found'
) AS col1_decoded, 
 length(col1)
from 
( select 1 as col1 
  union all select 2 as col1 
  union all select 3 as col1 
  union all select 4 as col1);

+------+--------------+--------------+
| COL1 | COL1_DECODED | LENGTH(COL1) |
+------+--------------+--------------+
| 1    |  first       | 1            |
| 2    |  second      | 1            |
| 3    |  not found   | 1            |
| 4    |  not found   | 1            |
+------+--------------+--------------+

However:

select col1, decode(col1, 
 1, 'first', 
 2, 'second', 
 3, 'third', 
 4, 'fourth', 
 'not found'
) AS col1_decoded, 
 length(col1)
from 
( select '1' as col1 
  union all select '2' as col1 
  union all select '3 ' as col1 
  union all select '4 ' as col1);

+------+--------------+--------------+
| COL1 | COL1_DECODED | LENGTH(COL1) |
+------+--------------+--------------+
|  1   |  first       | 1            |
|  2   |  second      | 1            |
|  3   |  third       | 2            |
|  4   |  fourth      | 2            |
+------+--------------+--------------+