JIYIK CN >

This article presents three alternative implementations that we can use as the equivalent of Oracle's decode() function in MySQL. To do this, we will use IF(), CASE, and a combination of FIELD() and ELT().

MySQL equivalent of Oracle's decode function

MySQL has its decode()function, but we will look at decode()an implementation that can be used as an alternative to Oracle's function.

Before jumping into the implementation, it is good to understand the difference between MySQL decode()functions and Oracle functions.decode()

MySQL decode() and Oracle decode()

The MySQL decode()function decodes the encoded string and outputs the original string. The Oracle decode() function compares the expression with each search term one by one.

If the expression is equal to the search value, Oracle Database will output the corresponding result. In Oracle decode(), if no match is found, it returns the default value, and if there is no default value, it returns NULL.

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

We also insert some data to learn clearly. In this tutorial, you can create and populate a table using the query given below to continue our learning.

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

Using CASE to simulate Oracle's decode() function in MySQL

Sample 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, comparing each expression to each search value and returning the corresponding result. Here, the default value of Beginner Level is returned if no match is found.

Using IF() in MySQL to simulate Oracle's decode() function

If we wish to split the result into binary values, we can use IF().

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

Combining FIELD and ELT to simulate Oracle's decode() function in MySQL

Sample 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, FIELD() outputs the position in the argument list of the string that matches LEVELS. Now, ELT() outputs a string from the argument list of ELT() at the position specified by FIELD().

For example, if LEVELS is 2, then FIELD(LEVELS,1,2,3,4)returns 2 because 2 is the second argument to FIELD (LEVEL is not evaluated here).

Then, ELT(2, 'Level One', 'Level Two', 'Level Three', 'Level Four')we return Level Two, which is the second argument to ELT() (don't count FIELD() as having an argument here).

In addition, if there is no matching LEVELS value in the list, IFNULL returns the default USER_LEVEL. In this way, we can combine ELT and FIELD to simulate Oracle's decode() function in MySQL.

Keep in mind that this solution may not be a good choice in terms of performance and readability, but it is a good option to explore MySQL's string functions.

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