DECODE
Searches for a code in a code-value list and returns the corresponding value.
Syntax
DECODE ( search_code, search_list [ , not_found_value ] ) search_list: code_value [, ...] code_value: code, value
Arguments
search_code
The code to search for in search_list
.
search_list
A list containing one or more code-value pairs. Each code maps to a value.
not_found_value
Optional. The value DECODE
returns if it doesn’t find search_code
in search_list
.
Remarks
If DECODE
does not find search_code
in search_list
and not_found_value
is not specified, the function returns NULL
.
Examples
In the following example, the search code, RI
, is a constant value.
SELECT DECODE('RI', 'NY', 'New York', 'RI', 'Rhode Island', 'WA', 'Washington', 'Unknown') AS 'state'; **** +--------------+ | state | +--------------+ | Rhode Island | +--------------+
In the following example, the search code, VA
, is a constant value and it is not in the search list. Consequently, the not found value is returned.
SELECT DECODE('VA', 'NY', 'New York', 'RI', 'Rhode Island', 'WA','Washington', 'Unknown') AS 'state'; **** +---------+ | state | +---------+ | Unknown | +---------+
The following example shows how to call DECODE
once per table row.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; CREATE TABLE person(name VARCHAR(80), state_abbrev VARCHAR(2)); INSERT INTO person VALUES ('John Jones', 'NY'), ('Mary Smith', 'RI'), ('Ann Brown', 'WA'); SELECT name, DECODE(state_abbrev, 'NY', 'New York', 'RI', 'Rhode Island', 'WA', 'Washington') AS state FROM person ORDER BY name; **** +------------+--------------+ | name | state | +------------+--------------+ | Ann Brown | Washington | | John Jones | New York | | Mary Smith | Rhode Island | +------------+--------------+
Implicit Collation
When character_set_server
is set to utf8
, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the utf8mb4
character set.
For more information, refer to Implicit Collation in Special Cases.