CODEWITHSUNDEEP
sql-servert-sql
sparkymark75
sparkymark75

Reputation: 593

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

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.

Upvotes: 5

Views: 7158

Answers (5)

Muthu
Muthu

Reputation: 83

I just modified @JoseTeixeira code using STRING_SPLIT function.

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 * from @table where EXISTS(SELECT [value] FROM string_split(@Filter, ',')
WHERE csv like '%' + [value] + '%') -- Table with filtered records

Upvotes: 0

Devart
Devart

Reputation: 122042

Try this one -

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

Upvotes: 0

user2290627
user2290627

Reputation:

What about this now? I didn't have used any User defined 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

Upvotes: 0

JoseTeixeira
JoseTeixeira

Reputation: 1306

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"

Upvotes: 4

user2290627
user2290627

Reputation:

You can use the PatIndex to find out the pattern in the field. Like the Following :

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

Upvotes: 0

Related Questions