MySQL SELECT LIKE or REGEXP to match multiple words in one record
Solution 1
Well if you know the order of your words.. you can use:
SELECT `name` FROM `table` WHERE `name` REGEXP 'Stylus.+2100'
Also you can use:
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%' AND `name` LIKE '%2100%'
Solution 2
I think that the best solution would be to use Regular expressions. It's cleanest and probably the most effective. Regular Expressions are supported in all commonly used DB engines.
In MySql there is RLIKE
operator so your query would be something like:
SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus|2100'
I'm not very strong in regexp so I hope the expression is ok.
Edit
The RegExp should rather be:
SELECT * FROM buckets WHERE bucketname RLIKE '(?=.*Stylus)(?=.*2100)'
More on MySql regexp support:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
Solution 3
You can just replace each space with %
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'
Solution 4
The correct solution is a FullText Search (if you can use it) https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
This nearly does what you want:
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus|2100)+.*(Stylus|2100)+';
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus|2100|photo)+.*(Stylus|2100|photo)+.*(Stylus|2100|photo)+.*';
But this will also match "210021002100" which is not great.
Solution 5
you need to do something like this,
SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus.*2100';
or
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus)+.*(2100)+';
Related videos on Youtube
Alessio Firenze
Updated on July 05, 2022Comments
-
Alessio Firenze almost 2 years
The field
table
.name
contains 'Stylus Photo 2100' and with the following querySELECT `name` FROM `table` WHERE `name` LIKE '%Stylus 2100%'
I get no results. Of course i would if i searched
SELECT `name` FROM `table` WHERE `name` LIKE '%Photo 2100%'
How can I select the record by searching 'Stylus 2100' ?
Thanks
-
Rick James over 5 yearsCaveat: This Question is about two words occurring in a particular order. Most of the Answers do not allow for matching "2100 Stylus".
-
-
Alessio Firenze over 12 yearsThanks but this way i wouldn't get if i searched 'Photo Stylus 2100'
-
Alessio Firenze over 12 yearsThanks, this way i won't get 'HP Laserjet 2100'. Sorry if my example was not the best.
-
mdprotacio over 12 yearshaha! okay, that requirement was not specified on the question, "How can I select the record by searching 'Stylus 2100' ?"
-
Jeff DQ about 11 yearsThis regular expression does something different: it gets anything that contains either 'Stylus' or '2100', not necessarily both.
-
Alkanshel over 10 yearsOne of the repliers to the answer here compared LIKE / REGEX performance and found that LIKE performed better, FYI: stackoverflow.com/questions/1127088/mysql-like-in
-
SERPRO over 10 years@Amalgovinus thanks for the link, I wouldn't have thought so.. It's good to know :)
-
Ondrej Bozek over 10 yearsI've improved my RegExp
-
velop almost 10 yearsI was searching for a regexp only solution and this trick did it ^^,
-
RTF about 9 yearsI know this question is old, but is this not the most efficient solution? From what I can tell, this will match
Photo Stylus 2100
. It will matchanything
followed byStylus
followed byanything
followed by2100
followed byanything
. If someone disagrees, please tell me why because it means I've fundamentally misunderstood something about like expressions. -
Florin over 8 yearsWhat if I have 3 words ?
-
SERPRO over 8 years@Florin in the first query
.+
indicate any character (1 or more) between Stylus and 2100 if you want to add EPSON it would beEPSON.+'Stylus.+2100
in the second query you just need to add a newAND
in the WHERE clause -
Florin over 8 yearsThanks @SERPRO. I know and I had tried ...but I am not getting the right result. But ... even with Stylus.+2100 I am not getting the right results ... with ^(.+EPSON.+2100.+)|^(.+2100.+EPSON.+).*$ I am geting the right LIKE results.
-
SERPRO over 8 years@Florin See these examples sqlfiddle.com/#!9/53dbec0/1/0 sqlfiddle.com/#!9/53dbec0/2/0
-
Robin Kanters about 8 yearswhy not
WHERE name LIKE '%Stylus%2100%'
? -
Admin almost 7 yearsBy adding a
|
between the 2 () like thisRLIKE '(?=.*Stylus)|(?=.*2100)'
will give separate results -->> rows with Stylus only, rows with 2100 only OR rows with both... but not necessarily the 2 in the same row... -
bobble bubble almost 7 yearsDid you try this? Never heard that MySql regex supports lookarounds.
-
tjbp over 6 years@RTF I'm a bit late, but % is like + in Regex, not *. It matches something, not anything, so won't match
Photo Stylus 2100
but would matchPhoto Stylus 2100x
. -
Rick James over 5 years@bobblebubble - Good point; I think MySQL 8.0 and MariaDB 10.0 are when they got 'lookarounds' and lots of other esoteric regexps.
-
Rick James over 5 years@tjbp - Wrong.
LIKE's
%
matches the empty string. So it is essentially identical toRLIKE's
.*
. Melvin's answer stands; Alessio first comment is wrong. It is trivial to test:SELECT 'Photo Stylus 2100' LIKE '%Stylus%2100%';
returns1
(meaningtrue
). -
Rick James over 5 yearsThe second one gives allows the silly
StylusStylus 210021002100
-- Why have the+
?? -
Rick James over 5 yearsThe first requires Stylus to come before 2100; the second lets them be in either order. So, they are "different" answers.
-
Adders over 5 yearsWhat about
SELECT name FROM table WHERE name REGEXP 'stylus|2100|photo'
-
Shakeel Ahmed over 2 yearsSELECT
name
FROMtable
WHEREname
RLIKE 'Stylus|2100'