82

The field table.name contains 'Stylus Photo 2100' and with the following query

SELECT `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

Xsi
  • 201
  • 2
  • 11
Alessio Firenze
  • 1,013
  • 1
  • 8
  • 13

7 Answers7

128

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%'
SERPRO
  • 10,015
  • 8
  • 46
  • 63
  • Thanks, this way i won't get 'HP Laserjet 2100'. Sorry if my example was not the best. – Alessio Firenze Feb 01 '12 at 17:09
  • 1
    One of the repliers to the answer here compared LIKE / REGEX performance and found that LIKE performed better, FYI: http://stackoverflow.com/questions/1127088/mysql-like-in – Alkanshel Nov 11 '13 at 18:07
  • @Amalgovinus thanks for the link, I wouldn't have thought so.. It's good to know :) – SERPRO Nov 12 '13 at 10:21
  • What if I have 3 words ? – Florin Dec 15 '15 at 07:24
  • @Florin in the first query `.+` indicate any character (1 or more) between Stylus and 2100 if you want to add EPSON it would be `EPSON.+'Stylus.+2100` in the second query you just need to add a new `AND` in the WHERE clause – SERPRO Jan 11 '16 at 15:24
  • Thanks @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. – Florin Jan 12 '16 at 06:19
  • @Florin See these examples http://sqlfiddle.com/#!9/53dbec0/1/0 http://sqlfiddle.com/#!9/53dbec0/2/0 – SERPRO Jan 12 '16 at 14:59
  • why not `WHERE name LIKE '%Stylus%2100%'`? – Robin Kanters May 10 '16 at 09:26
  • The first requires Stylus to come before 2100; the second lets them be in either order. So, they are "different" answers. – Rick James Jan 27 '19 at 23:25
  • What about `SELECT name FROM table WHERE name REGEXP 'stylus|2100|photo'` – Adders Feb 28 '19 at 09:09
  • SELECT `name` FROM `table` WHERE `name` RLIKE 'Stylus|2100' – Shakeel Ahmed Dec 18 '21 at 03:03
30

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

Ondrej Bozek
  • 10,987
  • 7
  • 54
  • 70
  • This regular expression does something different: it gets anything that contains _either_ 'Stylus' or '2100', not necessarily both. – Jeff DQ Jun 26 '13 at 05:57
  • I've improved my RegExp – Ondrej Bozek Mar 16 '14 at 22:44
  • 1
    I was searching for a regexp only solution and this trick did it ^^, – velop Jul 11 '14 at 12:46
  • By adding a `|` between the 2 () like this `RLIKE '(?=.*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... –  Jul 12 '17 at 01:57
  • Did you try this? Never heard that MySql regex supports lookarounds. – bobble bubble Jul 20 '17 at 16:02
  • @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 Jan 27 '19 at 23:00
18

You can just replace each space with %

SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'
mdprotacio
  • 842
  • 6
  • 18
  • Thanks but this way i wouldn't get if i searched 'Photo Stylus 2100' – Alessio Firenze Feb 01 '12 at 16:57
  • 2
    haha! okay, that requirement was not specified on the question, "How can I select the record by searching 'Stylus 2100' ?" – mdprotacio Feb 01 '12 at 17:17
  • I 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 match `anything` followed by `Stylus` followed by `anything` followed by `2100` followed by `anything`. If someone disagrees, please tell me why because it means I've fundamentally misunderstood something about like expressions. – RTF May 10 '15 at 12:39
  • 1
    @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 match `Photo Stylus 2100x`. – tjbp Oct 18 '17 at 15:44
  • 1
    @tjbp - Wrong. `LIKE's` `%` matches the empty string. So it is essentially identical to `RLIKE's` `.*`. Melvin's answer stands; Alessio first comment is wrong. It is trivial to test: `SELECT 'Photo Stylus 2100' LIKE '%Stylus%2100%';` returns `1` (meaning `true`). – Rick James Jan 27 '19 at 23:05
8

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.

Martin
  • 2,316
  • 1
  • 28
  • 33
1

you need to do something like this,

SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus.*2100';

or 

SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus)+.*(2100)+';
Mohideen bin Mohammed
  • 18,813
  • 10
  • 112
  • 118
0

Assuming that your search is stylus photo 2100. Try the following example is using RLIKE.

SELECT * FROM `buckets` WHERE `bucketname` RLIKE REPLACE('stylus photo 2100', ' ', '+.*');

EDIT

Another way is to use FULLTEXT index on bucketname and MATCH ... AGAINST syntax in your SELECT statement. So to re-write the above example...

SELECT * FROM `buckets` WHERE MATCH(`bucketname`) AGAINST (REPLACE('stylus photo 2100', ' ', ','));
0
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus % 2100%'
Nae
  • 14,209
  • 7
  • 52
  • 79
Gopalakrishnan
  • 1
  • 1