MaxCompute:DECODE

Syntax

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

Parameters

• expression: required. The expression that you want to compare.

• search: required. The search item that is used to compare with expression.

• result: required. The value that is returned when the value of search matches the value of expression.

• default: optional. If no search item matches the expression, the value of default is returned. If no value is specified for this parameter, null is returned.

Return value

• If a search item matches the expression, the return value is result.

• If no search item matches the expression, the value of default is returned.

• If no value is specified for the default parameter, null is returned.

• If duplicate search items match the expression, the value of the first search item is returned.

• In most cases, the return value is null when MaxCompute SQL calculates NULL=NULL. However, the DECODE function considers that the two null values are the same.

Examples

+------------+-------------+-------------+------------+------------+
| 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   |
+------------+-------------+-------------+------------+------------+

-- If the value of customer_id is c1, Taobao is returned. If the value is c2, Alipay is returned. If the value is c3, Aliyun is returned. If the value is null, N/A is returned. In other cases, Others is returned. 
select
decode(customer_id,
'c1', 'Taobao',
'c2', 'Alipay',
'c3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- The preceding statement is equivalent to the following statement: 
if customer_id = c1 then
result := 'Taobao';
elsif customer_id = c2 then
result := 'Alipay';
elsif customer_id = c3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

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

Syntax

string decode(binary <str>, string <charset>)

Parameters

• str: required. The string that you want to decode. The string is of the BINARY type.

• charset: required. The encoding format of the STRING type. Valid values: UTF-8, UTF-16, UTF-16LE, UTF-16BE, ISO-8859-1, and US-ASCII.

  Note

  Currently, the ISO-8859-1 and US-ASCII encoding formats can be used to encode or decode only English characters.

Return value

A value of the STRING type is returned. If the value of str or charset is null, the return value is null.

Examples

• Example 1: Encode and decode the string 中文样例 based on the UTF-8 format. Sample statements:

  -- Encode and decode the string.
  select decode(encode("中文样例", "UTF-8"), "UTF-8");
  -- The following result is returned:
  +-----+
  | _c0 |
  +-----+
  | 中文样例 |
  +-----+

• Example 2: An input parameter is set to null. Sample statements:

  -- The return value is null. 
  select decode(encode("中文样例","UTF-8"), null);
  | _c0 |
  +-----+
  | NULL |
  +-----+