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

Question

I have a table with boolean values ​​(only 0 and 1) that should be CSV-ed for the client. I know that I can do 1 replace as follows:

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

This converts all the values ​​of 1 to "Yes", but how to do it in one query for 1 => Yes and 0 => No, so the logical result is stored in one 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 to replace the row “No” (so the final result consisted of 4 columns, email, name, icon_clicked-> yes, icon_clicked-> no)

Answer 1

One way is to REPLACE :

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

or use CASE WHEN (this will work for most RDBMSs compared to the IF function, which is related to MySQL):

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

SqlFiddleDemo

EDIT:

There is also one good way to use ELT :

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

SqlFiddleDemo2

Answer 2

No need to use a nested Replace or Case statement. Try using IF , which is a lot easier.

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

• SQL FIDDLE DEMO