Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.


Searches for a code in a code-value list and returns the corresponding value.


DECODE ( search_code, search_list [ , not_found_value ] )

	search_list: code_value [, ...]

	code_value: code, value



The code to search for in search_list.


A list containing one or more code-value pairs. Each code maps to a 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 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;


| name       | state        |
| Ann Brown  | Washington   |
| John Jones | New York     |
| Mary Smith | Rhode Island |