DECODE
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
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_
The code to search for in search_
.
search_
A list containing one or more code-value pairs.
not_
Optional.DECODE
returns if it doesn’t find search_
in search_
.
Remarks
If DECODE
does not find search_
in search_
and not_
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.
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 stateFROM personORDER BY name;
+------------+--------------+
| name | state |
+------------+--------------+
| Ann Brown | Washington |
| John Jones | New York |
| Mary Smith | Rhode Island |
+------------+--------------+
Caution
Implicit Collation
When character_
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.utf8mb4
character set.
For more information, refer to Implicit Collation in Special Cases.
Last modified: February 24, 2023