按行分割表值函数

IF OBJECT_ID('dbo.Fun_SplitByLine') IS NOT NULL
	DROP FUNCTION dbo.Fun_SplitByLine
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-04-26
-- Description:	按行分割表值函数
-- =============================================
CREATE FUNCTION dbo.Fun_SplitByLine
(
	@str NVARCHAR(MAX)	
) 
RETURNS 
@t TABLE 
(
	rowNum INT,
	line   NVARCHAR(MAX)
)
AS
BEGIN
	DECLARE @i INT
	SET @i=1
	
	WHILE CHARINDEX(CHAR(10),@str)>0
	BEGIN
		INSERT INTO @t(rowNum,line)
		SELECT @i,SUBSTRING(@str,1,CHARINDEX(char(10),@str))
		SET @str = SUBSTRING(@str,CHARINDEX(char(10),@str)+1,LEN(@str))
		SET @i = @i+1
	END
	IF LEN(@str)>0
	BEGIN
		INSERT INTO @t(rowNum,line) VALUES(@i,@str)
	END

	UPDATE @t SET line=REPLACE(REPLACE(line,char(13),''),CHAR(10),'')
	
	RETURN 
END
GO
/*
SELECT * FROM dbo.Fun_SplitByLine('a
b
c
d') AS fs
rowNum	line
1		a

2		b

3		c

4		d
*/