CODEWITHSUNDEEP
mysqlsqlreplace
The One and Only ChemistryBlob
The One and Only ChemistryBlob

Reputation: 7884

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

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)

Upvotes: 7

Views: 22606

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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

Upvotes: 7

Related Questions