How to replace multiple values in 1 column in mysql SELECT query using REPLACE()?

Question

I have a table with Boolean values (0 and 1 only) that needs to be CSV-ed to a client. I know I can do 1 replace like this:

SELECT REPLACE(email, '%40', '@'),
       REPLACE(name,'%20', ' '),
       REPLACE(icon_clicked, 1, 'Yes') 
FROM myTable 
WHERE id > 1000;

This will convert all the values of 1 to 'Yes', but how to do this in a single query for both 1 => Yes and 0 => No so Boolean result is stored in a single column? I tried to do this:

SELECT REPLACE(email, '%40', '@'),
       REPLACE(name,'%20', ' '),
       REPLACE(icon_clicked, 1, 'Yes'),
       REPLACE(icon_clicked, 0, 'No')
FROM myTable
WHERE id > 1000;

But this query created an additional column for the 'No' string replace (so final result had 4 columns, email, name, icon_clicked->yes, icon_clicked->no)

Lukasz Szozda · Accepted Answer · 2015-12-28 20:13:46Z

7

One way is to nest REPLACE:

SELECT REPLACE(REPLACE(icon_clicked, 0, 'No'), 1, 'Yes')), ...
FROM myTable
...

or use CASE WHEN (this will work for most RDBMS comparing to IF function which is MySQL related):

SELECT CASE WHEN icon_clicked THEN 'Yes' ELSE 'No' END, ...
FROM myTable
...

SqlFiddleDemo

EDIT:

There is also one nice way utilizing ELT:

SELECT icon_clicked,
       ELT(FIELD(icon_clicked,0,1),'No','Yes'),
       ELT(icon_clicked + 1, 'No', 'Yes')
FROM mytable

SqlFiddleDemo2

edited Dec 28, 2015 at 20:13

answered Dec 28, 2015 at 16:46

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

Pரதீப் · Accepted Answer · 2018-04-03 15:49:49Z

4

No need to use nested Replace or Case statement. Try using IF, which is way simpler

SELECT 
   icon_clicked,
   IF(icon_clicked,'Yes','No')
FROM myTable

SQL FIDDLE DEMO

edited Apr 3, 2018 at 15:49

answered Dec 28, 2015 at 17:41

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

How to replace multiple values in 1 column in mysql SELECT query using REPLACE()?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related