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

mysql regex select sql-like
Alessio Firenze picture Alessio Firenze · Feb 1, 2012 · Viewed 180.3k times · Source

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

Answer

SERPRO picture SERPRO · Feb 1, 2012

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

Related questions

Finding line break and carriage return (\r\n) in MySQL

I can't seem to find any info about this on the internet (or I'm just not looking in the right direction). I have a few fields in my MySQL database containing a carriage return and line break \r\n . Is …

Read More
How to do a regular expression replace in MySQL?

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name; I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-] Now, is there …

Read More
How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More