MySQL Like multiple values

mysql sql sql-like
webmasters picture webmasters · Nov 13, 2010 · Viewed 309.8k times · Source

I have this MySQL query.

I have database fields with this contents

sports,shopping,pool,pc,games 
shopping,pool,pc,games 
sports,pub,swimming, pool, pc, games

Why does this like query does not work? I need the fields with either sports or pub or both?

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

Answer

jazkat picture jazkat · Sep 27, 2011

Faster way of doing this:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

is this: 

WHERE interests REGEXP 'sports|pub'

Found this solution here: http://forums.mysql.com/read.php?10,392332,392950#msg-392950

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Related questions

How can I search (case-insensitive) in a column using LIKE wildcard?

I looked around some and didn't find what I was after so here goes. SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM etc... How do …

Read More
Use LIKE %..% with field values in MySQL

I stumbled into a delicate SQL problem when I needed to use a value from a field inside a LIKE %..% statement. Example: SELECT t1.Notes, t2.Name FROM Table1 t1, Table2 t2 WHERE t1.Notes LIKE '%t2.Name%' …

Read More
how to use a like with a join in sql?

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before …

Read More