sql-server - 在循环中读取 Sql 服务器中的逗号分隔字符串

Question

我有这样的 nvarchar 数据:

'20030,20031,20033,20034,20065,20045,20044'

我想通过拆分成数组来读取所有这些，或者在拆分过程中逐一读取并进行进一步处理。

我已经尝试过这个good article但没能做到。

Answer 1

试试这个 article使用函数读取逗号分隔值并返回表格

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
--     ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

只需从存储过程调用该函数或仅调用如下函数:

SELECT items FROM [dbo].[Split] ('20030,20031,20033,20034,20065,20045,20044', ',')