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

by

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

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. How can I get column names from a table in SQL Server?
  5. Conditional WHERE clause in SQL Server
  6. How can I select the first day of a month in SQL?
  7. Split function equivalent in T-SQL?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Multi-statement Table Valued Function vs Inline Table Valued Function
  10. Select statement to find duplicates on certain fields
  11. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  12. Where value in column containing comma delimited values
  13. SQL query to split column data into rows
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Compare DATETIME and DATE ignoring time portion
  18. Split function in SQL Server 2008
  19. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Using PIVOT in SQL Server 2008
  23. How do I prevent a database trigger from recursing?
  24. How can I group by date time column without taking time into consideration
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. How to combine results of two queries into a single dataset
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. Cumulative Total in MS Sql server [duplicate]
  30. COALESCE Function in TSQL

Leave a Comment