How to SELECT using both wildcards (LIKE) and array (IN)?

Question

In SQL, if you want to perform a SELECT with a wildcard, you'd use:

SELECT * FROM table_name WHERE field_name LIKE '%value%'

If you wanted to use an array of possible values, you'd use:

SELECT * FROM table_name WHERE field_name IN ('one', 'two', 'three')

But, what would you do if you wanted to use both wildcards AND an array?

Kind of like:

SELECT * FROM table_name WHERE field_name LIKE IN ('%one', '_two', 'three[abv]')

If you're using MySQL, this is a possible duplicate: http://stackoverflow.com/questions/1127088/mysql-like-in — user254875486, Dec 15 '11 at 12:58
possible duplicate of [Using SQL LIKE and IN together](http://stackoverflow.com/questions/2318126/using-sql-like-and-in-together) — Ben, Sep 19 '13 at 21:15

score 13 · Accepted Answer · edited Dec 15 '11 at 14:05

13

SELECT *
FROM table_name
WHERE field_name LIKE '%one'
   OR field_name LIKE '_two'
   OR field_name LIKE 'three[abv]'

edited Dec 15 '11 at 14:05

Yuck

49,664
13
105
135

answered Dec 15 '11 at 13:00

LaGrandMere

10,265
1
33
41

I was hoping to avoid the overhead of this, but it would seem to be the only way. As it happens, even doing it this way causes a massive overhead as I have an array of a couple of hundred entries. Using just IN is fast. LIKE takes forever :/ – Dave Dec 15 '11 at 14:30

score 5 · Answer 2 · answered Dec 09 '15 at 12:11

you can use join with like statement like below query:

SELECT * FROM table_name t 
JOIN dbo.Split('one,two,three',',') s ON t.field_name LIKE N'%'+s.item+'%'

I create this function to split string:

CREATE FUNCTION [dbo].[Split] (@StringToSplit NVARCHAR(MAX), @SpliterChar CHAR(1))
RETURNS
    @returnList TABLE ([item] [NVARCHAR] (500))
AS
BEGIN
 DECLARE @nItem NVARCHAR(500);
 DECLARE @pos INT; 
 WHILE CHARINDEX(@SpliterChar, @StringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@SpliterChar, @StringToSplit); 
  SELECT @nItem = SUBSTRING(@StringToSplit, 1, @pos-1);
  if (@nItem <> '') INSERT INTO @returnList SELECT @nItem;
  SELECT @StringToSplit = SUBSTRING(@StringToSplit, @pos+1, LEN(@StringToSplit)-@pos);
 END
 if (@StringToSplit<>'') INSERT INTO @returnList SELECT @StringToSplit;
 RETURN
END

Nice one-liner. Nowadays this system function can be used: STRING_SPLIT ( string , separator [ , enable_ordinal ] ), so request will look like: SELECT * FROM table_name t JOIN STRING_SPLIT('one,two,three',',') s ON t.field_name LIKE N'%'+s.value+'%' — KEMBL, Jul 25 '23 at 16:53

How to SELECT using both wildcards (LIKE) and array (IN)?

2 Answers2

Linked