dragonn dragonn - 1 month ago 5
MySQL Question

SQL CASE in SELECT and WHERE for selecting rows

Is there any way to make this work:

SELECT *, CASE WHEN a=0 THEN "test1"
WHEN a=1 THEN "test2"
END AS a FROM table WHERE a LIKE "%test%"


So it should give me all rows where
a=0 or a=1
. The point is I need to search for these rows using string "test" .

Answer

You could try this:

SELECT * FROM
    (SELECT *,CASE WHEN a=0 THEN "test1"
                  WHEN a=1 THEN "test2"
             END AS b 
  FROM  TableA) TableB
WHERE b LIKE "%test%"

Sqlfiddle

In case if you want to override field:

SELECT * FROM
    (SELECT CASE WHEN a=0 THEN "test1"
          WHEN a=1 THEN "test2"
     ELSE a
     END AS a 
  FROM  TableA) TableA
WHERE a LIKE "%test%"

Sqlfiddle

Comments