Menu
And, many a time, you need to filter records using like conditions. Sometimes you need multiple conditions of matching or not matching. This post covers how to use the Multiple Like in Where clause of SQL. Not only LIKE, but you can also use multiple NOT LIKE conditions in SQL.
The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);
To select multiple values, you can use where clause with OR and IN operator.
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
The (a,b,c) list only works with in. For like, you have to use or:
WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
Why not you try REGEXP. Try it like this:
SELECT * FROM table WHERE interests REGEXP 'sports|pub'
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%')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With