The DECODE function has two uses depending on the provided parameters. It can be used as an IF-THEN-ELSE conditional branch or to decode a string from a specified encoding format. This topic describes the syntax and provides examples for both uses.
IF-THEN-ELSE branch selection
Syntax
DECODE(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])Parameters
Parameter | Required | Description |
expression | Yes | The expression to compare. |
search | Yes | The search item to compare with expression. |
result | Yes | The value to return if search and expression match. |
default | No | If no search items match, the default value is returned. If default is not specified, NULL is returned. |
All result values must have the same data type or be NULL. An error is returned if the data types are inconsistent.
The expression and all search values must have the same data type. Otherwise, an error is returned.
Return value
If a match is found, the corresponding result is returned.
If no match is found, the default value is returned.
If default is not specified, NULL is returned.
If a matching value appears in multiple search options, the result for the first occurrence is returned.
MaxCompute SQL typically returns NULL when evaluating
NULL=NULL. However, the DECODE function treats NULL as equal to NULL.
Example
A table named sale_detail contains the fields shop_name string, customer_id string, and total_price double. The table includes the following data:
+------------+-------------+-------------+------------+------------+
| 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 |
+------------+-------------+-------------+------------+------------+The following command is an example.
-- If customer_id is 'c1', return 'Taobao'. If it is 'c2', return 'Alipay'. If it is 'c3', return 'Aliyun'. If it is NULL, return 'N/A'. Otherwise, return 'Others'.
SELECT DECODE(customer_id, 'c1', 'Taobao', 'c2', 'Alipay', 'c3', 'Aliyun', Null, 'N/A', 'Others') AS RESULT FROM sale_detail;
-- This is equivalent to the following statement.
IF customer_id = c1 THEN RESULT := 'Taobao';
ELSEIF customer_id = c2 THEN RESULT := 'Alipay';
ELSEIF customer_id = c3 THEN RESULT := 'Aliyun'; ...
ELSE RESULT := 'Others';
END IF;The following result is returned:
+------------+
| result |
+------------+
| Others |
| Others |
| Others |
| Taobao |
| Alipay |
| Aliyun |
+------------+Decode a string from a specified format
Syntax
STRING DECODE(BINARY <str>, STRING <charset>)Parameters
Parameter | Required | Description |
str | Yes | BINARY type. The string to decode. |
charset | Yes | STRING type. The encoding format. Valid values: UTF-8, UTF-16, UTF-16LE, UTF-16BE, ISO-8859-1, and US-ASCII. Note The ISO-8859-1 and US-ASCII encoding formats support encoding and decoding for English characters only. Chinese characters are not supported. |
Return value
Returns a value of the STRING type. If str or charset is NULL, the function returns NULL.
Examples
Example 1: The following command encodes the string "Chinese sample" in UTF-8 format.
-- Encode and decode SELECT DECODE(ENCODE("Chinese sample","UTF-8"), "UTF-8");The following result is returned:
+-----+ | _c0 | +-----+ | Chinese sample | +-----+Example 2: An input parameter is NULL. The following command is run:
SELECT DECODE(ENCODE("Chinese sample","UTF-8"), NULL);The following result is returned:
+-----+ | _c0 | +-----+ | NULL | +-----+
Related functions
The DECODE function is classified as both a miscellaneous function and a string function. For more information about other miscellaneous functions, see Other functions. For more information about other string functions, see String functions.