如何在存储过程中使用循环拆分逗号分隔的字符串 [英] How to split the comma separated string with out using loop in stored procedure

问题描述

如何使用循环分割逗号分隔的字符串

How to split the comma separated string with out using loop

例如:-

我有逗号分隔的字符串，如 4,3,5,2,5,9

I have the string which has commma separated like 4,3,5,2,5,9

如果我想在不使用循环的情况下获取第三个逗号之后的字符串.

if i like to get the string after 3rd comma without using loop.

推荐答案

DECLARE @str varchar(MAX)='001,002,03,0004,05,6,07'
    ,@separator char(1)=','
    ,@n int = 3;

WITH str_nums ( id, n1, n2 ) 
AS 
( 
SELECT CAST(1 as int) as id, CAST(0 as bigint) as n1, CHARINDEX(@separator, @str+@separator) as n2
UNION ALL
SELECT id + 1, n2 as n1, CHARINDEX(@separator, @str+@separator, n2+1) as n2
FROM str_nums
WHERE n2<len(@str)
) 

SELECT SUBSTRING(@str, n1+1, n2-n1-1) as n_str
FROM str_nums
WHERE id = @n
GO

这篇关于如何在存储过程中使用循环拆分逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！