[mysql] MySQL Like multiple values

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

This question is related to mysql sql sql-like

The answer is


Why not you try REGEXP. Try it like this:

SELECT * FROM table WHERE interests REGEXP 'sports|pub'

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'

https://www.regular-expressions.info/anchors.html


You can also use REGEXP's synonym RLIKE as well.

For example:

SELECT *
FROM TABLE_NAME
WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'

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

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.


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.


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


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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-like

SQL Server: use CASE with LIKE Create hive table using "as select" or "like" and also specify delimiter How do I find ' % ' with the LIKE operator in SQL Server? Using LIKE operator with stored procedure parameters SQL- Ignore case while searching for a string Is the LIKE operator case-sensitive with MSSQL Server? Using Eloquent ORM in Laravel to perform search of database using LIKE SQL 'LIKE' query using '%' where the search criteria contains '%' How to use "like" and "not like" in SQL MSAccess for the same field? MySQL SELECT LIKE or REGEXP to match multiple words in one record