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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use IF   where in Oracle you would have used DECODE      mysql gt  select if emp id 1  X   Y   as test  emp id from emps

User · Answer

you can use if   in place of decode   in mySql as follows    This query will print all even id row   mysql gt  select id  name from employee where id in - gt   select if id 2 0 id null  from employee

[sql] MySQL equivalent of DECODE function in Oracle

Examples related to sql

Examples related to mysql

Examples related to database

Examples related to oracle