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

本文介绍了解析逗号分隔的字符串以生成 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 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