The Equivalent of Oracle's decode Function in MySQL

This tutorial presents the three alternative implementations that we can use as the equivalent of Oracle’s decode() function in MySQL. For that, we will use IF(), CASE, and the combination of FIELD() and ELT().

the Equivalent of Oracle’s Decode Function in MySQL

MySQL has its decode() function, but we are going to learn about the implementation that would be used as an alternative to Oracle’s decode() function.

Before going into the implementations, it is better to learn the difference between the MySQL decode() function and Oracle decode() function.

MySQL decode() vs Oracle decode()

MySQL decode() function decodes the encoded string and outputs an original string. While the Oracle decode() function compares an expression to every search item one by one.

The Oracle database outputs a corresponding result if the expression equals the search value. In Oracle decode(), the default value is returned if no match is found and returns NULL if there is no default value.

To practice it with a code example, we must have a table first. We create a table named users having ID, USERNAME, and LEVELS as attributes.

We also insert some data to learn clearly. In this tutorial, you may create and populate a table using the queries given below to move ahead with us.

Example Code (create and populate table):

# Create a table
CREATE TABLE users(
  ID INT NOT NULL AUTO_INCREMENT,
  USERNAME VARCHAR(45) NOT NULL,
  LEVELS INT NOT NULL,
  PRIMARY KEY (ID));

# Insert data
INSERT INTO users (USERNAME, LEVELS) VALUES
('mehvishashiq', 1),
('thomas011', 2),
('danielchristopher', 3),
('sairajames', 4);

SELECT * FROM users;

Output:

| ID   | USERNAME          | LEVELS |
| ---- | ----------------- | ------ |
| 1    | mehvishashiq      | 1      |
| 2    | thomas011         | 2      |
| 3    | danielchristopher | 3      |
| 4    | sairajames        | 4      |

Use CASE to Simulate Oracle’s decode() Function in MySQL

Example Code:

SELECT USERNAME,
CASE
  WHEN LEVELS = 1 THEN 'Level One'
  WHEN LEVELS = 2 THEN 'Level Two'
  WHEN LEVELS = 3 THEN 'Level Three'
  WHEN LEVELS = 4 THEN 'Level Four'
ELSE 'Beginner Level'
END AS USER_LEVEL
FROM users;

Output:

| USERNAME          | USER_LEVEL  |
| ----------------- | ----------- |
| mehvishashiq      | Level One   |
| thomas011         | Level Two   |
| danielchristopher | Level Three |
| sairajames        | Level Four  |

The CASE statement also emulates Oracle’s decode() function, compares each expression to every search value, and returns the corresponding result. Here, the default value Beginner Level is returned where there is no match found.

Use IF() to Simulate Oracle’s decode() Function in MySQL

If we want the results to be split into binary values, we can use IF().

Example Code:

SELECT USERNAME, IF(LEVELS >= 3,'Top Rates Plus','Top Rated') AS USER_LEVEL from users;

Output:

| USERNAME          | USER_LEVEL     |
| ----------------- | -------------- |
| mehvishashiq      | Top Rated      |
| thomas011         | Top Rated      |
| danielchristopher | Top Rated Plus |
| sairajames        | Top Rated Plus |

Use the Combination of FIELD & ELT to Simulate Oracle’s decode() Function in MySQL

Example Code:

SELECT USERNAME,
IFNULL(ELT( FIELD(LEVELS,1,2,3,4),
           'Level One', 'Level Two',
           'Level Three', 'Level Four'
        ),'Beginner Level')
As USER_LEVEL FROM users;

Output:

| USERNAME          | USER_LEVEL  |
| ----------------- | ----------- |
| mehvishashiq      | Level One   |
| thomas011         | Level Two   |
| danielchristopher | Level Three |
| sairajames        | Level Four  |

Here, the FIELD() outputs the string’s argument list position that matches LEVELS. Now, the ELT() outputs a string from the argument list of ELT() at a position specified by the FIELD().

For instance, if the LEVELS is 2, the FIELD(LEVELS,1,2,3,4) returns 2. Because 2 is a FIELD’s second argument (don’t count LEVEL here).

Then, the ELT(2, 'Level One', 'Level Two', 'Level Three', 'Level Four') returns Level Two, which is a second argument of ELT() (don’t count the FIELD() as an argument here).

Further, the IFNULL returns a default USER_LEVEL if no value of LEVELS is matched in the list. In this way, we can use the combination of ELT and FIELD to simulate Oracle’s decode() function in MySQL.

Remember, this solution may not be a good choice considering the performance and readability, but it is good to explore the string functions of MySQL.

Another worth noting point is that the FIELD() output is not case-sensitive. It means the FIELD('B','B') and FIELD('b','B') both return the same result which is 1.