You are viewing an older version of this section. View current production version.
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';
Output:
+--------------+
| 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';
Output:
+---------+
| state |
+---------+
| Unknown |
+---------+
The following example shows how to call DECODE once per table row.
DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_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;
Output:
+------------+--------------+
| name | state |
+------------+--------------+
| Ann Brown | Washington |
| John Jones | New York |
| Mary Smith | Rhode Island |
+------------+--------------+
