'LIKE ('%this%' OR '%that%') and something=else' not working
MysqlSqlSql LikeMysql Problem Overview
I have a select query where I am trying to search strings for multiple patterns
LIKE ('%this%' or '%that%' ) and something=else
Returns zero results
However
LIKE '%this%' and something=else
returns results and
LIKE '%that%' and something=else
returns result
Is it possible to get all my results into one query? If a string matches both, how will it handle that?
Mysql Solutions
Solution 1 - Mysql
It would be nice if you could, but you can't use that syntax in SQL.
Try this:
(column1 LIKE '%this%' OR column1 LIKE '%that%') AND something = else
Note the use of brackets! You need them around the OR expression.
Without brackets, it will be parsed as A OR (B AND C),which won't give you the results you expect.
Solution 2 - Mysql
Instead of using LIKE, use REGEXP. For example:
SELECT * WHERE value REGEXP 'THIS|THAT'
mysql> SELECT 'pi' REGEXP 'pi|apa'; -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa'; -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa'; -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$'; -> 0
Solution 3 - Mysql
Try something like:
WHERE (column LIKE '%this%' OR column LIKE '%that%') AND something = else
Solution 4 - Mysql
Break out the LIKE clauses into 2 separate statements, i.e.:
(fieldname1 LIKE '%this%' or fieldname1 LIKE '%that%' ) and something=else
Solution 5 - Mysql
Do you have something against splitting it up?
...FROM <blah>
WHERE
(fieldA LIKE '%THIS%' OR fieldA LIKE '%THAT%')
AND something = else
Solution 6 - Mysql
Have you tried:
(column LIKE '%this%' and something=else) or (column LIKE '%that%' and something=else)
Solution 7 - Mysql
I know it's a bit old question but still people try to find efficient solution so instead you should use FULLTEXT index (it's available from MySQL 5.6.4).
Query on table with +35mil records by triple like in where block took ~2.5s but after adding index on these fields and using BOOLEAN MODE inside match ... against ... it took only 0.05s.