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.....

I presume your actual usage is more complex, but in the example you gave you only need LIKE '%test%' since anything that matches '%testing%' also matches '%test%'. Also, if you have control over your database structure, you'd be better off normalizing the storage of keywords. — Larry Lustig, Mar 30 '10 at 21:40
@larry lustig: I was just giving a toy example... I want to do something little complex.. thanks — StackUnderflow, Mar 30 '10 at 21:54

Sam Saffron · Accepted Answer · 2010-03-30T21:37:36.907

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.

gbn · Answer 2 · 2010-03-30T21:42:11.057

3

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

edited Mar 30 '10 at 21:42

answered Mar 30 '10 at 21:35

gbn

422,506
82
585
676

@OMG Ponies: absolutely. Doh! – gbn Mar 30 '10 at 21:42
note on performance, best case (1 keyword) this will be as fast as the original. Worst case it will be a few times slower depending on row width due to the `distinct` – Sam Saffron Mar 30 '10 at 21:47
@Sam Saffron: it will be poor whichever way without proper text search – gbn Mar 30 '10 at 21:50

score 2 · Answer 3 · edited May 23 '17 at 12:19

2

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

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

edited May 23 '17 at 12:19

Community

1
1

answered Mar 30 '10 at 21:37

Daniel Vassallo

337,827
72
505
443

score 0 · Answer 4 · answered Mar 31 '10 at 12:01

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.

SQL IN Statement using like syntax?

4 Answers4