SQL IN Statement using like syntax?

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

Answer 3

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

Yes, Full Text Search. At least for prefix wildcards.

Answer 4

You can use Regular Expression to do this. In one Regular Expression you can define as many pattern as you want in one expression.

There are lot's of article about Regular Expression Matching in MS SQL SERVER. I used RLIKE in MySQL to do Regular Expression Matching.

I also attached link1 & link2 for MS SQL SERVER Regular Expression Matching.