CODEWITHSUNDEEP
mysqlregexselectsql-like
Alessio Firenze
Alessio Firenze

Reputation: 1063

MySQL SELECT LIKE or REGEXP to match multiple words in one record

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

Upvotes: 87

Views: 221698

Answers (7)

Gopalakrishnan
Gopalakrishnan

Reputation: 1

SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus % 2100%'

Upvotes: 0

user2560539
user2560539

Reputation:

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', ' ', ','));

Upvotes: 0

Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20137

you need to do something like this,

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

or 

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

Upvotes: 1

Ondrej Bozek
Ondrej Bozek

Reputation: 11481

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

Upvotes: 31

Martin
Martin

Reputation: 2581

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.

Upvotes: 8

SERPRO
SERPRO

Reputation: 10067

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%'

Upvotes: 132

mdprotacio
mdprotacio

Reputation: 842

You can just replace each space with %

SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'

Upvotes: 18

Related Questions