SWITCH with LIKE inside SELECT query in MySQL
Solution 1
Mysql supports two variants of case, the one you use in query 2 is less flexible but supports only equality on a single variable. The other version specifies no variable after case and then conditions need not be only equality:
select id_tag,
case
when tag LIKE "%class%" then "class"
when tag LIKE "%new%" then "new"
when tag LIKE "%pack%" then "pack"
end as matching_tag
from Tags
where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"
See documentation for further details
EDIT: Here's a bit more explanation on why your query #1 returned what it returned:
case tag
when tag LIKE "%class%" then "class"
when tag LIKE "%new%" then "new"
when tag LIKE "%pack%" then "pack"
end as matching_tag
expects to get a literal value for comparison between when ... then
In the above case the expressions tag LIKE "%class%"
, tag LIKE "%new%"
and tag LIKE "%pack%"
are all evaluated before the actual case comparison.
However (!), what happens is that they become either 0 or 1 and when compared to the value of tag it is the first value of 0 that will match any char (char will get cast to 0) - this is consistent with the results of your first query.
Here's a query that shows the logical values for the relevant expressions:
select id_tag, tag LIKE "%class%", tag LIKE "%new%", tag = 0, case tag when tag LIKE "%class%" then "class" when tag LIKE "%new%" then "new" when tag LIKE "%pack%" then "pack" end as matching_tag from Tags where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%";
That's why you get unexpected results; the silent CAST is a standard pitfall here.
Solution 2
Just want remind, about else clause:
case
when tag LIKE "%class%" then "class"
when tag LIKE "%new%" then "new"
when tag LIKE "%pack%" then "pack"
else "no one"
end as matching_tag
Sandeepan Nath
Organizer of meetup group PHP Micro Meetups (Pune) About me I am a Software Developer/Designer from India. Here is my Brief Résumé on Stackoverflow Careers. Contact me at sandeepan (dot) nits (at) google's mail. Other than programming I have deep interests in Human Psychology, Entrepreneurship, Social work and Scientific Research. While younger I had deep interests in Genetics, Astronomy etc. I love computer games, especially FPS games on multi-player, cricket, music, racing and adventure. I am a 24 years old guy and as time is passing by, I am gradually coming to realize that there are so many things to see and do in this beautiful world. I am afraid that a few years down the line I will repent over so many things I wished to do but could not do. However, I have started serious planning for all this.
Updated on March 28, 2020Comments
-
Sandeepan Nath about 4 years
I have this Tags table
CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`), KEY `tag_4` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2937 ; INSERT INTO `Tags` (`id_tag`, `tag`) VALUES (1816, '(class'), (2642, 'class\r\n\r\nâ?¬35'), (1906, 'class\r\nif'), (1398, 'class'), (2436, 'class)'), (1973, 'class:\n1.'), (2791, 'classes'), (1325, 'New'), (2185, 'pack'), (1905, 'packed'), (1389, 'WebClass');
I want to fetch all records where tag matches keywords
class
orpack
ornew
, along with another field which indicates which of the 3 keywords actually matched with the tag field.The following query does not give correct results Query 1
select id_tag, case tag when tag LIKE "%class%" then "class" when tag LIKE "%new%" then "new" when tag LIKE "%pack%" then "pack" end as matching_tag from Tags where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"
I have to use the like inside the case. Otherwise complete matching works. The following query works:-
Query 2
select id_tag, case tag when "class" then "class" when "new" then "new" when "pack" then "pack" end as matching_tag from Tags where tag = "class" OR tag = "new" OR tag = "pack"
What is wrong with the query 1. Please help.