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
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%'
.+ 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 clauseI 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
You can just replace each space with %
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'
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.
Photo Stylus 2100 but would match Photo Stylus 2100x.
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).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.
you need to do something like this,
SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus.*2100';
or
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus)+.*(2100)+';
StylusStylus 210021002100 -- Why have the +??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', ' ', ','));
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus % 2100%'
select * from table1 where column1 REGEXP 'something|otherthing';