MySQL LIKE IN() and using AND

Question

I am trying to condense the below query which could have unlimited options based on a web form field. Users can input as many title keywords separated by commas. So my query would be constructed like the below with unlimited arguments:

"SELECT title FROM titles WHERE 
 title LIKE '%blue%' 
 AND
 title like '%red%'
 AND
 title like '%green%'
 AND
 title like '%yellow%'
 ...";

Looking at this link, MySQL LIKE IN()?, it shows a good example of using regular expression, but it is using "OR".

SELECT * from fiberbox where field REGEXP '1740|1938|1940';

What is the equivalent of using "AND"? For example, in quasi-SQL:

SELECT * from titles WHERE title REGEXP 'blue && red && green && yellow';

This would be a lot easier if you split out the keywords into a separate table and just had to do `SELECT title_id FROM title_keywords WHERE keyword IN (?,?,...) GROUP BY title_id HAVING COUNT(*)=?` where you check that N rows were matched for N keywords. — tadman, Nov 29 '12 at 17:16
take a look here: http://stackoverflow.com/questions/6820139/regexp-and-mysql-is-there-an-and-operator — jcho360, Nov 29 '12 at 18:36
Tadman: your suggestion does not work because using "IN" is too specific. I need it to use wildcards. For example, someone might want to search "yell", which should pick up on "yellow". Using "IN" means it specifically chooses the word "yell" and not "yellow". — user785179, Nov 29 '12 at 19:10
Regular expressions don't really have an "AND" the way you are thinking about it. Everything in the regular expression is essentially "AND"ed together unless expressly made optional, or a list of alternatives are given in a group (with the "|"). This could be done with a regular expression, but unless the search options are in predictable positions in the title (e.g., blue, red, green and yellow ALWAYS appear in the title in that specific order), the expression will be extremely complicated. Assuming the contents of your titles are not well structured, I think your current sol'n is good enuf. — RobertB, Nov 29 '12 at 19:23

histocrat · Accepted Answer · 2012-11-29T19:32:15.313

AND is a bit more difficult to do with regexes, and it doesn't seem to be possible with MySQL's limited regular expression syntax. If you are using the myISAM engine or MySQL 5.6, you can use the MATCH() function to get most of what you want:

SELECT title FROM titles WHERE MATCH (title)
AGAINST ('+red* +green* +yellow*' IN BOOLEAN MODE);

However, I would recommend sticking to your simple chaining of ANDs. It's less fragile, more powerful, and easier to code.

MySQL LIKE IN() and using AND

1 Answers1