# 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.

```sql
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.

```sql
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.

```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;

```

```output

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

```

> **⚠️ Warning**: ## Implicit CollationWhen `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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/special-cases.md).

***

Modified at: February 24, 2023

Source: [/db/v9.1/reference/sql-reference/conditional-functions/decode/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/conditional-functions/decode/)

(An index of the documentation is available at /llms.txt)