Implements different features based on parameters. You can use the DECODE function to implement branch selection of IF-THEN-ELSE. You can also use the DECODE function to decode a string based on the specified encoding format. This topic provides syntax instructions for the features and application examples in relevant scenarios.
Implement IF-THEN-ELSE branch selection
Syntax
DECODE(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])Parameters
Parameter | Required | Description |
expression | Yes | The expression that you want to compare. |
search | Yes | The search item to be compared with the expression. |
result | Yes | The value returned if the values of search and expression match. |
default | No | If no search items match the expression, the value of default is returned. If this parameter is not specified, NULL is returned. |
Except for the NULL values, all other values of the result parameter must be of the same data type. If the values are of different data types, an error is returned.
The values of search and expression must be of the same data type. Otherwise, an error is returned.
Return value
If a search item matches the expression, the value of result is returned.
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 result value for 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
The sale_detail table contains the shop_name STRING, customer_id STRING, and total_price DOUBLE columns. This table contains 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 |
+------------+-------------+-------------+------------+------------+Sample statements:
-- If the value of customer_id is c1, the return value is Taobao. If the value is c2, the return value is Alipay. If the value is c3, the return value is Aliyun. If the value is NULL, the return value is N/A. In other cases, the return value is Others.
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';
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 based on the specified encoding format
Syntax
STRING DECODE(BINARY <str>, STRING <charset>)Parameters
Parameter | Required | Description |
str | Yes | The string that you want to decode. The string is of the BINARY type. |
charset | Yes | The encoding format. The value is of the STRING type. 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 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 English Sample based on the UTF-8 format. Sample statement:
-- Encode and decode the string. SELECT DECODE(ENCODE("English Sample","UTF-8"), "UTF-8");The following result is returned:
+-----+ | _c0 | +-----+ | English Sample | +-----+Example 2: Set either of the input parameters to NULL. Sample statement:
SELECT DECODE(ENCODE("English Sample","UTF-8"), NULL);The following result is returned:
+-----+ | _c0 | +-----+ | NULL | +-----+
Related functions
The DECODE function belongs to both other functions and string functions. For more information, see Other functions and String functions.