Search code examples
mysqlsqlsql-like

How do I shorten multiple LIKEs

My query:

SELECT *
FROM tbl
WHERE (
  col LIKE '%this%' AND 
  col LIKE '%that%' AND 
  col LIKE '%something else%'
)

The strings I'm searching for are NOT in a predictable order.

Is there a way to write this without all those LIKE lines?

Solution

  • Instead of this:

    WHERE col LIKE '%this%' AND col LIKE '%that%' AND col like '%something else%'

    you may try this for faster way: 

    WHERE col REGEXP 'this|that|somethingelse'

    but be aware that this is only applicable if there is a specific pattern else I dont think that it can be shortened.

    EDIT:-

    As your updated question says if the strings which you are searching are not in a predictable order then I dont think that there is any way to shorten your LIKE statements. You have to use it in the same way in which you are using it right now.