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);
SQL Server 根据分隔符依次截取每个子字符串
最新推荐文章于 2023-01-07 10:05:16 发布