本文介绍了解析逗号分隔的字符串以生成 Where 子句中的 IN 字符串列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的存储过程收到一个参数,它是一个逗号分隔的字符串:
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?
推荐答案创建这个函数 (sqlserver 2005+)
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使用这个声明
SELECT * FROM yourtable WHERE account in (SELECT val FROM dbo.f_split(@account, ','))将我的拆分功能与 XML 拆分进行比较:
Comparing my split function to 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 bXML:
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 secondsf_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
更多推荐
解析逗号分隔的字符串以生成 Where 子句中的 IN 字符串列表
发布评论