DECODE

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.

SQL

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.

SQL

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.

SQL

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

Caution

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.