Outdated Version

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