Parse comma-separated string to make IN List of strings in the Where clause

Question

My stored procedure receives a parameter which is a comma-separated string:

DECLARE @Account AS VARCHAR(200) SET @Account = 'SA,A' 

I need to make from it this statement:

WHERE Account IN ('SA', 'A') 

What is the best practice for doing this?

Answer 1

Create this function (sqlserver 2005+)

CREATE function [dbo].[f_split] ( @param nvarchar(max),  @delimiter char(1) ) returns @t table (val nvarchar(max), seq int) as begin set @param += @delimiter  ;with a as ( select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq union all select t + 1, charindex(@delimiter, @param, t + 1), seq + 1 from a where charindex(@delimiter, @param, t + 1) > 0 ) insert @t select substring(@param, f, t - f), seq from a option (maxrecursion 0) return end 

use this statement

SELECT * FROM yourtable  WHERE account in (SELECT val FROM dbo.f_split(@account, ',')) 

---

Comparing my split function to XML split:

Testdata:

select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a  from master..spt_values a cross join master..spt_values b 

XML:

 SELECT count(t.c.value('.', 'VARCHAR(20)'))  FROM (      SELECT top 100000 x = CAST('<t>' +             REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)            from a  ) a  CROSS APPLY x.nodes('/t') t(c)  Elapsed time: 1:21 seconds 

f_split:

select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')  Elapsed time: 43 seconds 

This will change from run to run, but you get the idea

Answer 2

Try this one -

DDL:

CREATE TABLE dbo.Table1 (       [EmpId] INT     , [FirstName] VARCHAR(7)     , [LastName] VARCHAR(10)     , [domain] VARCHAR(6)     , [Vertical] VARCHAR(10)     , [Account] VARCHAR(50)     , [City] VARCHAR(50) )  INSERT INTO dbo.Table1 ([EmpId], [FirstName], [LastName], [Vertical], [Account], [domain], [City]) VALUES       (345, 'Priya', 'Palanisamy', 'DotNet', 'LS', 'Abbott', 'Chennai'),      (346, 'Kavitha', 'Amirtharaj', 'DotNet', 'CG', 'Diageo', 'Chennai'),      (647, 'Kala', 'Haribabu', 'DotNet', 'DotNet', 'IMS', 'Chennai') 

Query:

DECLARE @Account VARCHAR(200) SELECT @Account = 'CG,LS'  SELECT * FROM Table1 WHERE [Vertical] = 'DotNet' AND (ISNULL(@Account, '') = '' OR Account IN (      SELECT t.c.value('.', 'VARCHAR(20)')      FROM (          SELECT x = CAST('<t>' +                 REPLACE(@Account, ',', '</t><t>') + '</t>' AS XML)      ) a      CROSS APPLY x.nodes('/t') t(c) )) 

Output:

Extended statistics:

SSMS SET STATISTICS TIME + IO:

XML:

(3720 row(s) affected) Table 'temp'. Scan count 3, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   SQL Server Execution Times:    CPU time = 187 ms,  elapsed time = 242 ms. 

CTE:

(3720 row(s) affected) Table '#BF78F425'. Scan count 360, logical reads 360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'temp'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   SQL Server Execution Times:    CPU time = 281 ms,  elapsed time = 335 ms.