MySQL replace result value with another value

Question

I have MySQL and the query is:

select name,re_type from myTable

I want to replace all values of type:

1 = student
2 = teacher

So the result should be:

name    re_type
---------------
Ron     Student
Mac     Teacher

Not like:

name    re_type
---------------
Ron     1
Mac     2

Is it possible to make a query like that so I get the desired result in MySQL ?

Kickstart · Accepted Answer · 2012-10-12 09:13:51Z

9

You can use a CASE statement

SELECT name, CASE WHEN re_type = 1 THEN 'Student' WHEN re_type = 2 THEN 'Teacher' ELSE 'Donno' END AS re_type_text
FROM myTable

answered Oct 12, 2012 at 9:13

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VeZoul · Accepted Answer · 2012-10-12 09:14:57Z

1

You can use a joint table that will store labels for your re_type like

re_type_id  re_type_label
1           student
2           teacher

And alter your query by :

select t.name,l.re_type_label
from myTable t
inner join labelsTable l
    on l.re_type_id = t.re_type

answered Oct 12, 2012 at 9:14

VeZoul

5206 silver badges19 bronze badges

Comments

Andrea Ligios · Accepted Answer · 2012-10-12 09:25:34Z

1

I think he wants to keep IDs on re_type field, and just decoding them when extracting.

You can use CASE WHEN or ELT(), MySql equivalent to Oracle's Decode(), like explained here

, but the Best Practice is to create an external table containing re_type and re_type_description fields, so if tomorrow you'll have new values, you don't have to change all your queries.

answered Oct 12, 2012 at 9:25

Andrea Ligios

50.4k29 gold badges127 silver badges256 bronze badges

Collectives™ on Stack Overflow

MySQL replace result value with another value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related