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);