MSSQL函数用逗号分隔字符串转换成一列数据

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION [FN_SHARE_SPLIT](@C VARCHAR(max),@SPLIT VARCHAR(2))
RETURNS @T TABLE(COL VARCHAR(50))
AS
BEGIN
WHILE(CHARINDEX(@SPLIT,@C)<>0)
BEGIN
INSERT @T(COL) VALUES (SUBSTRING(@C, 1, CHARINDEX(@SPLIT, @C) - 1))
SET @C = STUFF(@C, 1, CHARINDEX(@SPLIT, @C), '')
END
INSERT @T(COL)VALUES (@C)
RETURN
END

 

测试用例

SELECT * FROM dbo.FN_SHARE_SPLIT('1,2,3,4,5,6',',')