SQL CTE 递归分割以逗号分隔的字符串

declare @t table (id INT IDENTITY,sentence VARCHAR(2000))
INSERT INTO @t SELECT 'AAA,BBB,CCC'

SELECT * FROM @t 
 
 ;WITH mycte AS 
(
    SELECT id,sentence,mstart=1,mend=CHARINDEX(',',sentence)+1,num=1 FROM @t
    UNION ALL 
    SELECT id,sentence,mstart=mend,CHARINDEX(',',sentence+',',mend)+1,num=num+1 FROM mycte
    WHERE mend<=LEN(sentence)
)
SELECT STUFF(SUBSTRING(sentence,mstart,mend-mstart-1),1,0,'') sentence FROM mycte

如果由excel中直接复制过来的，可能在没一个项前会有换行符，替换方法为 replace  char(13) + char(10)

更简单的可以转换为xml数据格式

Create FUNCTION [dbo].[分隔字符] 
(
@str VARCHAR(8000),
@strSplit VARCHAR(10)
)
RETURNS 
@re TABLE 
(
id int IDENTITY(1,1),
col varchar(200)
)
AS
BEGIN
    
    DECLARE    @splitL int;
    SET @splitL=LEN(@strSplit+'a')-2
    WHILE CHARINDEX(@strSplit,@str)>0
    BEGIN
        INSERT @re(col)VALUES(LEFT(@str,CHARINDEX(@strSplit,@str)-1))
        SET @str=STUFF(@str,1,CHARINDEX(@strSplit,@str)+@splitL,'')        
    END
    INSERT INTO @re(col)VALUES(@str)
    RETURN 
END

split函数

 

以上代码由“土豆”提供，感谢

转载于:https://www.cnblogs.com/meteortent/p/3328798.html