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

Question

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

User · Answer

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

User · Answer

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

User · Answer

you need to do something like this   SELECT   FROM buckets WHERE bucketname RLIKE  Stylus  2100     or   SELECT   FROM buckets WHERE bucketname RLIKE   Stylus     2100

User · Answer

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

User · Answer

SELECT  name  FROM  table  WHERE  name  LIKE   Stylus   2100

User · Answer

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

User · Answer

You can just replace each space with    SELECT  name  FROM  table  WHERE  name  LIKE   Stylus 2100

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

Examples related to mysql

Examples related to regex

Examples related to select

Examples related to sql-like