如何使用 SQL Server 用逗号分隔字符串? [英] How to separate string by comma using SQL Server?

问题描述

我想用逗号分隔字符串.其实我想过滤 WHERE 子句像

I wants to separate string by comma. Actually I wants to filter in WHERE Clause like

CREATE PROC spGetRecords
@class varchar(50)
AS
BEGIN
    SELECT *
    FROM SampleTable
    WHERE class in (@class)  --in Database class is an integer
END

所以，我想在执行如下查询时传递参数

So, I want to pass the parameter when execute query like below

spGetRecords @class = '12,22,45,66'

我认为在我的情况下不可能在单个参数中传递多个值.

I think it's not possible to pass multiple value in single parameter in my case.

所以，如果我删除了由 ',' 分隔的字符串，那么我可以在 while 循环中运行我的查询，这将使记录正确?

So, If I remove separate the string by ',' then I can run my query in while loop that will bring the record correct?

那么，如何用逗号

推荐答案

您可以尝试使用xml路径进行拆分，如下所示:

You can try splitting using xml path as below:

Declare @delimiter nvarchar(max) = ','
Declare @str nvarchar(max) = '12,22,45,66'

Declare @xml as xml
        Select @xml = CAST('<x>' + REPLACE((SELECT REPLACE(@str,@delimiter,'$$$SSText$$$') AS [*] FOR XML PATH('')),'$$$SSText$$$','</x><x>')+ '</x>' AS XML) 

--select @xml
Select y.value(N'text()[1]', N'nvarchar(MAX)') as value 
FROM @xml.nodes(N'x') as x(y) 

如果您在 SQL Server >= 2016 中，您可以使用 STRING_SPLIT() 如下:

If you are in SQL Server >= 2016 you can use STRING_SPLIT() as below:

Select * from string_split('12,22,45,66',',')

这篇关于如何使用 SQL Server 用逗号分隔字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！