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

--根据分隔符截取字符串 '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);