SQL Server 根据分隔符依次截取每个子字符串

最新推荐文章于 2025-11-01 16:03:42 发布

原创最新推荐文章于 2025-11-01 16:03:42 发布 · 8.8k 阅读

3 ·

CC 4.0 BY-SA版权

SQL Server 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了两种使用SQL递归查询来高效拆分逗号分隔的字符串的方法。第一种方法利用了CHARINDEX函数和CTE（公共表表达式）递归地定位分隔符，并通过LAG窗口函数获取前一个分隔符的位置，从而实现字符串的有效拆分。第二种方法则是通过生成一系列数字，结合CHARINDEX函数来定位分隔符并进行拆分。

--根据分隔符截取字符串 'Chris,Nancy,Alvin,Sherman,Erick,Josie,Sonia,Joanna'
--法1 根据CHARINDEX函数递归求出分隔符在字符串中出现的位置,之后根据分隔符截取字符串
--注意:下例中当字符内没有分隔符时会陷入死循环
DECLARE @str VARCHAR(1000)='Chris,Nancy,Alvin,Sherman,Erick,Josie,Sonia,Joanna';
WITH TEMP AS (
SELECT CHARINDEX(',',@str,0) ROWNUM
UNION ALL
SELECT CASE WHEN CHARINDEX(',',@str,ROWNUM+1)=0 THEN len(@str)+1 ELSE CHARINDEX(',',@str,ROWNUM+1) END ROWNUM
FROM TEMP
WHERE ROWNUM<len(@str)
)
SELECT RN=ROW_NUMBER() OVER(ORDER BY ROWNUM)
--,PrivousRN=isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)
--,ROWNUM
--,(ROWNUM-isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)) SplitLen
,SUBSTRING(@str,isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)+1,(ROWNUM-isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0))-1) SplitSTR
FROM TEMP
OPTION(MAXRECURSION 0);

--修改版
DECLARE @str VARCHAR(1000)='Chris,Nancy,Alvin,Sherman,Erick,Josie,Sonia,Joanna';
WITH TEMP AS (
SELECT CHARINDEX(',',@str+',',0) ROWNUM
UNION ALL
SELECT CHARINDEX(',',@str+',',ROWNUM+1) ROWNUM
FROM TEMP
WHERE ROWNUM<LEN(@str)
)
SELECT RN=ROW_NUMBER() OVER(ORDER BY ROWNUM)
--,PrivousRN=isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)
--,ROWNUM
--,(ROWNUM-isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)) SplitLen
,SUBSTRING(@str,isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0)+1,(ROWNUM-isNull(LAG(ROWNUM) OVER(ORDER BY ROWNUM),0))-1) SplitSTR
FROM TEMP
OPTION(MAXRECURSION 0);

--法2 根据递归先求得一串序列,再根据序列使用charindex函数截取依次靠近分隔符的字符串,之后通过WHERE条件中判断分隔符位置筛选目标字符串
DECLARE @str VARCHAR(1000)='Chris,Nancy,Alvin,Sherman,Erick,Josie,Sonia,Joanna';
WITH TEMP AS 
( 
SELECT 1 AS NUM 
UNION ALL 
SELECT NUM+1 
FROM TEMP 
WHERE NUM<len(@str)
) 
SELECT RN=ROW_NUMBER() OVER(ORDER BY NUM)
,SplitSTR=SUBSTRING(@str,NUM,CHARINDEX(',',@str+',',NUM)-NUM) 
from TEMP 
WHERE SUBSTRING(','+@str,NUM,1)=',' 
OPTION(MAXRECURSION 0);