MySQL Like multiple values
MysqlSqlSql LikeMysql Problem Overview
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%')
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
The (a,b,c) list only works with in. For like, you have to use or:
WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
Solution 3 - Mysql
Why not you try REGEXP. Try it like this:
SELECT * FROM table WHERE interests REGEXP 'sports|pub'
Solution 4 - Mysql
You can also use REGEXP's synonym RLIKE as well.
For example:
SELECT *
FROM TABLE_NAME
WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'
Solution 5 - Mysql
Don't forget to use parenthesis if you use this function after an AND parameter
Like this:
WHERE id=123 and(interests LIKE '%sports%' OR interests LIKE '%pub%')
Solution 6 - Mysql
Your query should be SELECT * FROM `table` WHERE find_in_set(interests, "sports,pub")>0
What I understand is that you store the interests in one field of your table, which is a misconception. You should definitively have an "interest" table.
Solution 7 - Mysql
Or if you need to match only the beginning of words:
WHERE interests LIKE 'sports%' OR interests LIKE 'pub%'
you can use the regexp caret matches:
WHERE interests REGEXP '^sports|^pub'
Solution 8 - Mysql
Like @Alexis Dufrenoy proposed, the query could be:
SELECT * FROM `table` WHERE find_in_set('sports', interests)>0 OR find_in_set('pub', interests)>0
More information in the manual.
Solution 9 - Mysql
More work examples:
SELECT COUNT(email) as count FROM table1 t1
JOIN (
SELECT company_domains as emailext FROM table2 WHERE company = 'DELL'
) t2
ON t1.email LIKE CONCAT('%', emailext) WHERE t1.event='PC Global Conference";
Task was count participants at an event(s) with filter if email extension equal to multiple company domains.