How to replace multiple values in 1 column in mysql SELECT query using REPLACE()?
Solution 1
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
...
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
Solution 2
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
The One and Only ChemistryBlob
Most Useful Tools for My Daily Dev Work https://jsonlint.com https://jsfiddle.net https://cssburner.com http://phptester.net http://jsondiff.com https://base64decode.org https://gtmetrix.com https://minifier.org https://diffchecker.com
Updated on April 04, 2020Comments
-
The One and Only ChemistryBlob about 4 years
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)