DECODE

DECODE ( search_code, search_list [ , not_found_value ] )

    search_list: code_value [, ...]

    code_value: code, value

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.

If DECODE does not find search_code in search_list and not_found_value is not specified, the function returns NULL.

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