Sql table comma separated values contain any of variable values checking

Question

I have a variable @a='1,2,3,4' and a table that contain a column B that contain comma separated values.

How can I check that column B values contain any of the @a variable values?

Answer 1

You need to implement a function for splitting the values. There are a lot of variations, you can use this:

CREATE FUNCTION [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder](@List nvarchar(max), @Delimiter nvarchar(10) = ',')
RETURNS @result TABLE 
(   
    [Value] nvarchar(max),
    [SortOrder] bigint NOT NULL
)
AS
BEGIN
    IF @Delimiter is null 
    BEGIN
        SET @Delimiter = ','
    END
    DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@List, @Delimiter, ']]></r><r><![CDATA[') + ']]></r>'

    DECLARE @BufTable TABLE (Value nvarchar(max), SortOrder bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY)
    INSERT INTO @BufTable (Value)
        SELECT Tbl.Col.value('.', 'nvarchar(max)')
        FROM   @xml.nodes('//r') Tbl(Col)
        OPTION (OPTIMIZE FOR (@xml = NULL)) 

    INSERT INTO @result (Value, SortOrder)
        SELECT Value, SortOrder
        FROM @BufTable

    RETURN
END

Having such function, its pretty easy:

DECLARE @DataSource TABLE
(
    [column] VARCHAR(1024)
);

DECLARE @column VARCHAR(1024) = '1,2,3,4';

INSERT INTO @DataSource ([column])
VALUES ('100,200,300')
      ,('100,1,500')
      ,('1,2,3,500')
      ,('200')
      ,('33,32,31,4,30');

SELECT DISTINCT [column]
FROM @DataSource
CROSS APPLY [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] ([column], ',') DSV
INNER JOIN [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] (@column, ',') FV
    ON DSV.[Value] = FV.[Value];

Using CROSS APPLY we are splitting the values for each column. Then we are splitting the filtering values and performing INNER JOIN in order to match only the rows having a value contained in the filter value. After that, we need a DISTINCT because column value may contains many values from the filter.

Answer 2

A t-sql string "splitter" is what you need but I would NOT use the mTVF recommended above as it is extremely inefficient and will kill parallelism. An inline table valued function (iTVF) is what you want for splitting strings.

I would suggest using delimitedSplit8k or delimitedSplit8k_lead which will perform ~30-90 times faster; or STRING_SPLIT if you're on SQL 2016+ and only need the value which will be several hundred times faster. Note this performance test:

-- sample data
declare @rows int = 10000;
if object_id('tempdb..#strings') is not null drop table #strings;
select top (@rows) 
  someid     = identity(int,1,1),
  somestring = replace(right(left(cast(newid() as varchar(36)), 27),21),'-',',')
into #strings
from sys.all_columns a, sys.all_columns b;

-- Performance test
set nocount on;
print 'fn_Analysis_ConvertCsvListToNVarCharTableWithOrder'+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @item varchar(10);
  select @item = [value]
  from #strings t
  cross apply dbo.fn_Analysis_ConvertCsvListToNVarCharTableWithOrder(t.somestring,',');
print datediff(ms,@st,getdate());
go 5

print 'delimitedSplit8K (serial)'+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @item varchar(10);
  select @item = item
  from #strings t
  cross apply dbo.DelimitedSplit8K(t.somestring,',')
  option (maxdop 1);
print datediff(ms,@st,getdate());
go 5

print 'delimitedSplit8K (parallel)'+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @item varchar(10);
  select @item = item
  from #strings t
  cross apply dbo.DelimitedSplit8K(t.somestring,',')
  option (recompile, querytraceon 8649);
print datediff(ms,@st,getdate());
go 5

Results

fn_Analysis_ConvertCsvListToNVarCharTableWithOrder
--------------------------------------------------
Beginning execution loop
4183
4274
4536
4294
4406
Batch execution completed 5 times.
delimitedSplit8K (serial)
--------------------------------------------------
Beginning execution loop
50
50
50
54
53
Batch execution completed 5 times.
delimitedSplit8K (parallel)
--------------------------------------------------
Beginning execution loop
133
134
133
140
136
Batch execution completed 5 times.

How you could use to solve your problem

declare @sometable table(someid int identity, someNbr tinyint);
insert @sometable values (1),(3),(6),(12),(7),(15),(19);

declare @searchstring varchar(1000) = '1,2,3,4,19';
select someid, someNbr
from @sometable t
cross apply dbo.DelimitedSplit8K(@searchstring,',') s
where t.someNbr = s.Item;

Results

someid      someNbr
----------- -------
1           1
2           3
7           19