Regexp in mysql - find the line that contains ALL keywords (AND operator)

Question

I was stuck for 3 hours, I guessed a simple thing, but searched the Internet and could not find the answer. I have 1, 2, 3, 4 or more keywords and a search string for keywords. I am looking for a regexp expression that resolves ALL keywords. For example:

string = "this car is red and has big wheels"

keywords: car wheels

return: true

keywords: car for wheels

return: true

keywords: car red

return: true

keywords: it is big

return: true

keywords: car red small

return: false (there is no "small" word in the line)

I use a mysql query like this:

SELECT name, desc FROM table WHERE CONCAT(name, desc) REGEXP ($keyword1)($keyword2) 

But it returns an empty string. What should be the correct regexp syntax?

Answer 1

The easiest solution is to move AND from the regular expression and into SQL:

 SELECT name, desc FROM table WHERE CONCAT(name, desc) REGEXP ($keyword1) AND CONCAT(name, desc) REGEXP ($keyword2) 

Otherwise, your regular expression must include all N! possible orders in a long chain of OR operators, and this length grows very, very fast (with 5 keywords, you will need to create 120 different orders!)