【SQL SERVER】Fn_SplitStr 逗号相隔的字符串返回多行

CREATE FUNCTION [dbo].[Fn_SplitStr]
(
@param VARCHAR(MAX), 
@delimiter CHAR(1) = ','
)
RETURNS @t TABLE (val varchar(100), seq INT)
AS
BEGIN
SET @param += @delimiter;
WITH a AS
(
SELECT CAST(1 AS BIGINT) f, CHARINDEX(@delimiter, @param) t, 1 seq
UNION ALL
SELECT t + 1, CHARINDEX(@delimiter, @param, t + 1), seq + 1
FROM a
WHERE CHARINDEX(@delimiter, @param, t + 1) > 0
)
INSERT @t
SELECT SUBSTRING(@param, f, t - f) , seq FROM a
OPTION (MAXRECURSION 0)
RETURN
END
GO

运行结果如下：