SQL IN Statement using like syntax? [duplicate]

Question

I would like to do something like this i.e., use wild card characters in the in clause:

SELECT * FROM mytable WHERE keywords IN ('%test%', '%testing%')

This is not supported in SQL Server.... Is there some other way to achieve it...

Looking for something other than:

SELECT * FROM mytable WHERE keywords like '%test%' or keywords like '%testing%' or.....

Answer 1

Nope, only other way I can think of is joining in to a temp table but then you have to eliminate duplicate rows.

This query of yours is horribly inefficient as it will unconditionally table scan. Perhaps you should look at separating the keywords or introducing a full text index.

If you would like to look into doing full text searches, then I would recommend looking into some of the heavy weights out there like sphinx or lucene when you evaluate the Sql Server full-text solution.

Answer 2

SELECT DISTINCT *
FROM
  mytable M
  JOIN
  (
  SELECT '%test%' AS pattern
  UNION ALL SELECT '%testing%'
  ...
  ) foo ON M.keywords LIKE foo.Pattern

Could be a CTE or temp table too