# How the DECODE() function works in Mariadb?

The `DECODE()`

function is a built-in function that can be used to decrypt data that was encrypted using the `ENCODE()`

function or any other compatible encryption function.

The `DECODE()`

function is a built-in function that can be used to decrypt data that was encrypted using the `ENCODE()`

function or any other compatible encryption function. The `DECODE()`

function can operate in two modes: the default mode and the Oracle mode. The default mode is compatible with MySQL, while the Oracle mode is compatible with Oracle Database. The Oracle mode was introduced in Mariadb 10.3.2 and can be enabled by setting the `sql_mode`

system variable to `ORACLE`

. The `DECODE_ORACLE()`

function is a synonym for the Oracle-mode version of the function, and is available in all modes.

## Syntax

The syntax of the `DECODE()`

function is as follows:

```
DECODE(crypt_str, pass_str)
```

In the default mode, the function takes two arguments:

`crypt_str`

: This is a binary string that contains the encrypted data. It should be a string returned from`ENCODE()`

or any other compatible encryption function.`pass_str`

: This is a string that contains the password used to encrypt the data. It should be the same password that was used in the encryption function.

The function returns a binary string that contains the decrypted data. The resulting string will be the original string only if `pass_str`

is the same as the password used in the encryption function.

In the Oracle mode, the syntax of the `DECODE()`

function is as follows:

```
DECODE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr])
```

The function takes one or more arguments:

`expr`

: This is an expression that is compared to the search expressions.`search_expr`

: This is an expression that is compared to`expr`

. There can be more than one search expression, separated by commas.`result_expr`

: This is an expression that is returned if`expr`

matches the corresponding`search_expr`

. There should be one result expression for each search expression, separated by commas.`default_expr`

: This is an optional expression that is returned if`expr`

does not match any of the search expressions. If this argument is omitted, the function returns`NULL`

in this case.

The function returns an expression that depends on the comparison of `expr`

and the search expressions. The function compares `expr`

to the search expressions, in order. If it finds a match, the function returns the corresponding result expression. If no matches are found, the function returns the default expression, or `NULL`

if no default is provided. `NULL`

s are treated as equivalent in the comparison.

## Examples

In this section, we will show some examples of how to use the `DECODE()`

function in both modes.

### Example 1: Decoding data encrypted with `ENCODE()`

Suppose we have a table called `users`

that contains two columns: `id`

and `password`

. The column `password`

has some encrypted values as shown below:

```
SELECT * FROM users;
+----+------------------+
| id | password |
+----+------------------+
| 1 | 8C9F4E7D0C6B1F0F |
| 2 | 8C9F4E7D0C6B1F0F |
| 3 | 8C9F4E7D0C6B1F0F |
| 4 | 8C9F4E7D0C6B1F0F |
| 5 | 8C9F4E7D0C6B1F0F |
+----+------------------+
```

We can decrypt the password column by using the `DECODE()`

function with the correct password as follows:

```
SELECT id, DECODE(password, 'secret') AS password
FROM users;
+----+----------+
| id | password |
+----+----------+
| 1 | admin |
| 2 | user1 |
| 3 | user2 |
| 4 | user3 |
| 5 | user4 |
+----+----------+
```

The result is a table that shows the original passwords for each user. The `DECODE()`

function returns the original string only if the password is the same as the one used in the `ENCODE()`

function.

### Example 2: Decoding data encrypted with `AES_ENCRYPT()`

Suppose we have a table called `messages`

that contains two columns: `id`

and `content`

. The column `content`

has some encrypted values as shown below:

```
SELECT * FROM messages;
+----+----------------------------------+
| id | content |
+----+----------------------------------+
| 1 | 0x8C9F4E7D0C6B1F0F |
| 2 | 0x8C9F4E7D0C6B1F0F |
| 3 | 0x8C9F4E7D0C6B1F0F |
| 4 | 0x8C9F4E7D0C6B1F0F |
| 5 | 0x8C9F4E7D0C6B1F0F |
+----+----------------------------------+
```

We can decrypt the content column by using the `DECODE()`

function with the correct password as follows:

```
SELECT id, DECODE(content, 'secret') AS content
FROM messages;
+----+-----------------+
| id | content |
+----+-----------------+
| 1 | Hello world! |
| 2 | How are you? |
| 3 | I am fine. |
| 4 | Nice to meet you|
| 5 | Bye bye. |
+----+-----------------+
```

The result is a table that shows the original messages for each row. The `DECODE()`

function returns the original string only if the password is the same as the one used in the `AES_ENCRYPT()`

function.

### Example 3: Decoding data using the Oracle mode

Suppose we have a table called `grades`

that contains two columns: `id`

and `score`

. The column `score`

has some numeric values as shown below:

```
SELECT * FROM grades;
+----+-------+
| id | score |
+----+-------+
| 1 | 90 |
| 2 | 80 |
| 3 | 70 |
| 4 | 60 |
| 5 | 50 |
+----+-------+
```

We can use the `DECODE()`

function in the Oracle mode to assign a letter grade to each score as follows:

```
SET sql_mode = 'ORACLE';
SELECT id, score, DECODE(score, 90, 'A', 80, 'B', 70, 'C', 60, 'D', 'F') AS grade
FROM grades;
+----+-------+-------+
| id | score | grade |
+----+-------+-------+
| 1 | 90 | A |
| 2 | 80 | B |
| 3 | 70 | C |
| 4 | 60 | D |
| 5 | 50 | F |
+----+-------+-------+
```

The result is a table that shows the letter grade for each score. The `DECODE()`

function compares the score to the search expressions, in order. If it finds a match, it returns the corresponding result expression. If no matches are found, it returns the default expression, which is ‘F’ in this case.

## Related Functions

The `DECODE()`

function is related to the following functions:

`ENCODE()`

: This function encrypts a string using a password and returns a binary string. It can be used with the`DECODE()`

function in the default mode to encrypt and decrypt data.`AES_ENCRYPT()`

: This function encrypts a string using the AES algorithm and a password and returns a binary string. It can be used with the`DECODE()`

function in the default mode to encrypt and decrypt data.`AES_DECRYPT()`

: This function decrypts a binary string that was encrypted using the AES algorithm and a password and returns a string. It can be used as an alternative to the`DECODE()`

function in the default mode to decrypt data encrypted with`AES_ENCRYPT()`

.`CASE`

: This is a control flow statement that can be used to return different values based on conditions. It can be used as an alternative to the`DECODE()`

function in the Oracle mode to perform conditional logic.

## Conclusion

The `DECODE()`

function is a versatile function that can be used to decrypt data or perform conditional logic, depending on the mode. The default mode is compatible with MySQL, while the Oracle mode is compatible with Oracle Database. The `DECODE()`

function can be used with various encryption functions, such as `ENCODE()`

and `AES_ENCRYPT()`

, to encrypt and decrypt data. The `DECODE()`

function can also be used with various expressions.