mssql - what is the function of sql server
解析以逗號分隔的字符串,以使IN子句中的IN字符串列表 (2)
創建此功能(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
這將從一次運行變為運行,但你明白了
我的存儲過程接收一個以逗號分隔的字符串的參數:
DECLARE @Account AS VARCHAR(200)
SET @Account = 'SA,A'
我需要從中做出這樣的聲明:
WHERE Account IN ('SA', 'A')
這樣做的最佳做法是什麼?
最有效的方法是使用CLR函數進行拆分字符串。 有關示例和性能比較,請參閱此文章