MySQL equivalent of DECODE function in Oracle

Question

I am trying to find an equivalent of DECODE function in MySQL. It works like this:

Select Name, DECODE(Age,
       13,'Thirteen',14,'Fourteen',15,'Fifteen',16,'Sixteen',
       17,'Seventeen',18,'Eighteen',19,'Nineteen',
       'Adult') AS AgeBracket
FROM Person

The DECODE function will compare value of column 'Age' with 13, 14, 15.. and return appropriate string value 'Thirteen', 'Fourteen'.. and if it matches with nothing, then default value of 'Adult' will be returned.

Any ideas which function in MySQL can do this job? Thanks.

CLARIFICATION: I agree using CASE is one way of achieving desired result, but I am rather looking for a function because of performance and other reasons.

Mysql's ELT seems to be a DECODE alterntive: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_elt — JAR.JAR.beans, Sep 25 '13 at 09:31

score 60 · Answer 1 · edited Oct 21 '12 at 15:38

60

You can use IF() where in Oracle you would have used DECODE().

mysql> select if(emp_id=1,'X','Y') as test, emp_id from emps;

edited Oct 21 '12 at 15:38

askmish

6,464
23
42

answered May 23 '12 at 09:58

Lokesh Kumar

601
5
2

6

this only works for boolean expressions, not arbitrary length cases like `DECODE()` does. – krock Sep 24 '18 at 10:14
but presumably you could nest it if you wanted? – Andy Jul 08 '21 at 11:59

score 24 · Answer 2 · answered Oct 24 '14 at 22:25

Select Name, 
case 
  when Age = 13 then 'Thirteen'
  when Age = 14 then 'Fourteen'
  when Age = 15 then 'Fifteen'
  when Age = 16 then 'Sixteen'
  when Age = 17 then 'Seventeen'
  when Age = 18 then 'Eighteen'
  when Age = 19 then 'Nineteen'
  else 'Adult'
end as AgeBracket
FROM Person

score 19 · Answer 3 · edited Jun 05 '17 at 09:12

19

You can use a CASE statement...however why don't you just create a table with an integer for ages between 0 and 150, a varchar for the written out age and then you can just join on that

edited Jun 05 '17 at 09:12

Mahesh Bansod

1,493
2
24
42

answered Jan 16 '11 at 15:23

SQLMenace

132,095
25
206
225

Seth Difley · Answer 4 · 2016-06-27T19:30:45.780

Another MySQL option that may look more like Oracle's DECODE is a combination of FIELD and ELT. In the code that follows, FIELD() returns the argument list position of the string that matches Age. ELT() returns the string from ELTs argument list at the position provided by FIELD(). For example, if Age is 14, FIELD(Age, ...) returns 2 because 14 is the 2nd argument of FIELD (not counting Age). Then, ELT(2, ...) returns 'Fourteen', which is the 2nd argument of ELT (not counting the FIELD() argument). IFNULL returns the default AgeBracket if no match to Age is found in the list.

Select Name, IFNULL(ELT(FIELD(Age,
       13, 14, 15, 16, 17, 18, 19),'Thirteen','Fourteen','Fifteen','Sixteen',
       'Seventeen','Eighteen','Nineteen'),
       'Adult') AS AgeBracket
FROM Person

While I don't think this is the best solution to the question either in terms of performance or readability it is interesting as an exploration of MySQL's string functions. Keep in mind that FIELD's output does not seem to be case sensitive. I.e., FIELD('A','A') and FIELD('a','A') both return 1.

score 5 · Answer 5 · answered Apr 22 '16 at 08:19

The example translates directly to:

Select Name, CASE Age
       WHEN 13 then 'Thirteen' WHEN 14 then 'Fourteen' WHEN 15 then 'Fifteen' WHEN 16 then 'Sixteen'
       WHEN 17 then 'Seventeen' WHEN 18 then 'Eighteen' WHEN 19 then 'Nineteen'
       ELSE 'Adult' END AS AgeBracket
FROM Person

which you may prefer to format e.g. like this:

Select Name,
       CASE Age
         when 13 then 'Thirteen'
         when 14 then 'Fourteen'
         when 15 then 'Fifteen'
         when 16 then 'Sixteen'
         when 17 then 'Seventeen'
         when 18 then 'Eighteen'
         when 19 then 'Nineteen'
         else         'Adult'
       END AS AgeBracket
FROM Person

score 1 · Answer 6 · answered Jan 17 '11 at 14:57

1

If additional table doesn't fit, you can write your own function for translation.

The plus of sql function over case is, that you can use it in various places, and keep translation logic in one place.

answered Jan 17 '11 at 14:57

Danubian Sailor

1
38
145
223

score 0 · Answer 7 · answered Oct 06 '17 at 06:18

0

you can use if() in place of decode() in mySql as follows This query will print all even id row.

mysql> select id, name from employee where id in
-> (select if(id%2=0,id,null) from employee);

answered Oct 06 '17 at 06:18

Niteesh Kumar

1
1

Lukas Eder · Answer 8 · 2023-01-09T09:45:31.770

While the other answers work correctly in case there are no NULL values involved, here's a NULL safe answer (because that's what Oracle's DECODE really does):

SELECT
  Name, 
  CASE
    WHEN Age <=> 13 THEN 'Thirteen'
    WHEN Age <=> 14 THEN 'Fourteen'
    WHEN Age <=> 15 THEN 'Fifteen'
    ...
  END AS AgeBracket
FROM Person

Might be worth noting that MySQL "compatible" RDBMS have imitated Oracle's function:

score -9 · Answer 9 · edited Mar 02 '18 at 00:53

Try this:

Select Name, ELT(Age-12,'Thirteen','Fourteen','Fifteen','Sixteen',
   'Seventeen','Eighteen','Nineteen','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
   'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult') AS AgeBracket FROM Person

MySQL equivalent of DECODE function in Oracle

9 Answers9

Linked

Related