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

 1. ***--根据递归先求得一串序列,再根据序列使用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);