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

Question

I have a table in which there is a column containing a row of comma separated values ​​(CSV).

eg. VALUE1, VALUE2, VALUE3

Passing as a filter is a different set of CSVs.

eg. VALUE2, VALUE3

So, in the above example, the query should return any records where any 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 above example, rows 1, 2, 3, 5, and 6 should be returned by the query, since they all contain VALUE1 or VALUE2.

Answer 1

If I understood this, this will solve this:

You create a function to 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 review the description of "return any sheet containing any quotation marks in the filter"

Answer 2

You can use PatIndex to find out the pattern in the field. As in the following:

 SELECT * FROM @TABLE WHERE PATINDEX('%' +@FILTER +'%',CSV)>0 

Answer 3

Try this option -

 DECLARE @table TABLE (rownum INT, csv NVARCHAR(300)) INSERT INTO @table VALUES (1,'VALUE1, VALUE2, VALUE3') , (2,'VALUE1, VALUE2') , (3,'VALUE1, VALUE3') , (4,'VALUE2, VALUE3') , (5,'VALUE1, VALUE2, VALUE3') , (6,'VALUE3, VALUE1, VALUE2') , (7,'VALUE2, VALUE1') DECLARE @Filter NVARCHAR(50) SELECT @Filter = 'VALUE1,VALUE2' ;WITH cte AS ( SELECT token = SUBSTRING( t.string , n.number + 1 , ABS(CHARINDEX(',', t.string, n.number + 1) - n.number - 1)) FROM ( SELECT string = ',' + @Filter ) t CROSS JOIN [master].dbo.spt_values n WHERE n.[type] = 'p' AND n.number <= LEN(t.string) AND SUBSTRING(t.string, n.number, 1) = ',' ) SELECT DISTINCT rownum, csv FROM cte JOIN @table ON PATINDEX('%'+ token +'%', CSV) = 0 

Answer 4

How about this now? I did not use any custom functions at all.

 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 VARCHAR(50) DECLARE @IN VARCHAR(MAX) SET @FILTER='VALUE1,VALUE2' SET @IN=REPLACE(@FILTER,',','%') SET @IN='%' +@IN +'%' SELECT @IN SELECT * FROM @table WHERE PATINDEX(@IN,RTRIM(LTRIM(CSV))) >0