How to compare the values in a column to a long list in SQL Server

Question

Assuming my table is T and my column is C, I need to check if any substring in C has any of these values: Orange,Apple,Banana,Kiwi,Strawberry,Blueberry.

My actual list of values is over 80 in length and I would like to try to avoid the following:

 Select * from T
 Where C LIKE '%Orange%'
 OR C LIKE '%Apple%'
 OR C LIKE '%Banana%'
 OR C LIKE '%Kiwi%'
 OR C LIKE '%Strawberry%'
 OR C LIKE '%Blueberry%'

Is there a way to insert my values into a table and compare it to that? I make this comparison multiple times in my query.

score 1 · Accepted Answer · answered Oct 19 '17 at 16:08

This is one of those unusual situations where I would use a function to convert my comma separated list into a table and then join to it:

CREATE FUNCTION [dbo].[udf_ConvertStringArrayToTable] (
@StringArray    VARCHAR(MAX)
)

RETURNS @StringTable    TABLE
(
String  VARCHAR(500)
)

AS
BEGIN

DECLARE @Index  INT
SET @StringArray = LTRIM(RTRIM(@StringArray))
WHILE LEN(@StringArray) > 0
BEGIN

SET @Index = CHARINDEX(',', @StringArray, 1)
IF @Index = 0 BEGIN SET @Index = LEN(@StringArray) + 1 END

INSERT INTO @StringTable
SELECT SUBSTRING(@StringArray, 1, @Index - 1)

If @Index > LEN(@StringArray) BEGIN SET @StringArray = '' END
ELSE BEGIN SET @StringArray = SUBSTRING(@StringArray, @Index + 1, LEN(@StringArray) - @Index) END

END

RETURN

END

Then you could call it like this:

DECLARE @Example VARCHAR(MAX)
SET @Example = 'Orange,Apple,Banana,Kiwi,Strawberry,Blueberry' -- etc

SELECT
*
FROM
udf_ConvertStringArrayToTable(@Example) E
INNER JOIN T ON T.C LIKE '%' + E.String + '%'

How to compare the values in a column to a long list in SQL Server

1 Answers1