How to filter records when filter is a comma separated list of values

Question

I have a table which has a column in it containing a string of comma separated values (CSV).

e.g. VALUE1, VALUE2, VALUE3

Being passed in as a filter is another set of CSV.

e.g. VALUE2, VALUE3

So in the example above, the query should return any records where any of the filter values are in the CSV column.

Example

declare @table table
(
    rownum int,
    csv nvarchar(300)
)

insert into @table values (1,'VALUE1, VALUE2, VALUE3')
insert into @table values (2,'VALUE1, VALUE2')
insert into @table values (3,'VALUE1, VALUE3')
insert into @table values (4,'VALUE3, VALUE4')
insert into @table values (5,'VALUE1, VALUE2, VALUE3')
insert into @table values (6,'VALUE3, VALUE4, VALUE2')
insert into @table values (7,'VALUE3')

declare @Filter nvarchar(50)

set @Filter = 'VALUE1,VALUE2'

select * from @table

So in the example above, rows 1, 2, 3, 5 and 6 should be returned by a query as they all contain either VALUE1 or VALUE2.

Answer 1

If I well understood it, this will solve it:

You create a function to do the split:

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END
GO

Then you can do this:

declare @table table
(
    rownum int,
    csv nvarchar(300)
)

insert into @table values (1,'VALUE1, VALUE2, VALUE3')
insert into @table values (2,'VALUE1, VALUE2')
insert into @table values (3,'VALUE1, VALUE3')
insert into @table values (4,'VALUE2, VALUE3')
insert into @table values (5,'VALUE1, VALUE2, VALUE3')
insert into @table values (6,'VALUE3, VALUE1, VALUE2')
insert into @table values (7,'VALUE2, VALUE1')

declare @Filter nvarchar(50)

set @Filter = 'VALUE3,VALUE4'

select * from @table
SELECT * INTO #FilterTable FROM ufn_CSVToTable(@Filter, ',')
SELECT * FROM #FilterTable
select * from @table where EXISTS(SELECT String FROM #FilterTable WHERE csv like '%' + String + '%')
DROP TABLE #FilterTable

I'm considering the description "return any rown containing any of the calues in the filter"