MaxCompute:DECODE

Syntax

DECODE(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

Parameters

Unlike standard SQL CASE expressions — which treat NULL = NULL as false — DECODE considers two NULL values equal. A NULL expression matches a NULL search value and returns the corresponding result.

Return value

Returns result when a matching search is found, default when no match is found, or NULL if default is not specified.

The search and expression values must be of the same data type; otherwise, an error is returned.

Examples

The following examples use the sale_detail table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Example 1: Map values with a default

Map customer_id to a label, and return Others for any unrecognized value. A NULL customer_id matches the NULL search value and returns N/A.

SELECT DECODE(customer_id,
              'c1', 'Taobao',
              'c2', 'Alipay',
              'c3', 'Aliyun',
              NULL, 'N/A',
              'Others') AS result
FROM sale_detail;

Result:

+------------+
| result     |
+------------+
| Others     |
| Others     |
| Others     |
| Taobao     |
| Alipay     |
| Aliyun     |
+------------+

Example 2: Omit the default

When default is omitted and no search matches, DECODE returns NULL.

SELECT DECODE(customer_id,
              'c1', 'Taobao',
              'c2', 'Alipay',
              'c3', 'Aliyun') AS result
FROM sale_detail;

Result:

+------------+
| result     |
+------------+
| Taobao     |
| Alipay     |
| Aliyun     |
| NULL       |
| NULL       |
| NULL       |
+------------+

The rows with customer_id values c5, c6, and c7 have no matching search entry, so they return NULL.

Syntax

STRING DECODE(BINARY <str>, STRING <charset>)

Parameters

Return value

Returns a value of the STRING type. Returns NULL if str or charset is NULL.

Examples

Example 1: Encode and decode a string in UTF-8

SELECT DECODE(ENCODE("English Sample", "UTF-8"), "UTF-8");

Result:

+-----------------+
| _c0             |
+-----------------+
| English Sample  |
+-----------------+

Example 2: NULL parameter

If either parameter is NULL, the result is NULL.

SELECT DECODE(ENCODE("English Sample", "UTF-8"), NULL);

Result:

+------+
| _c0  |
+------+
| NULL |
+------+