CODEWITHSUNDEEP
sqlmysqldatabaseoracle-database
Ali
Ali

Reputation: 5609

MySQL equivalent of DECODE function in Oracle

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.

Upvotes: 50

Views: 193148

Answers (9)

Lukas Eder
Lukas Eder

Reputation: 220762

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:

Upvotes: 0

vitali_y
vitali_y

Reputation: 510

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

Upvotes: -9

Niteesh Kumar
Niteesh Kumar

Reputation: 1

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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134941

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

Upvotes: 19

Seth Difley
Seth Difley

Reputation: 1330

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.

Upvotes: 10

user6239614
user6239614

Reputation: 51

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

Upvotes: 5

Allen Kenney
Allen Kenney

Reputation: 521

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

Upvotes: 26

Lokesh Kumar
Lokesh Kumar

Reputation: 611

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;

Upvotes: 61

Cjxcz Odjcayrwl
Cjxcz Odjcayrwl

Reputation: 22847

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.

Upvotes: 1

Related Questions