解析逗号分隔的字符串以在Where子句中生成IN字符串列表

Question

我的存储过程收到一个参数，该参数是逗号分隔的字符串：DECLARE @Account AS VARCHAR(200)SET @Account = 'SA,A'我需要从中作出这样的陈述：WHERE Account IN ('SA', 'A')最佳做法是什么？

Answer 1

创建此函数（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

使用此语句

SELECT *

FROM yourtable 

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

比较我的split函数和XML split：

测试数据：

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

这会随着时间的流逝而变化，但是您会明白

Answer 2

最有效的方法是对拆分字符串使用CLR函数。请参阅本文以获取示例和性能比较