--根据分隔符截取字符串 '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);
SQL Server 根据分隔符依次截取每个子字符串
最新推荐文章于 2024-06-12 19:20:06 发布