按行分割表值函数

IF OBJECT_ID('dbo.Fun_SplitByLine') IS NOT NULLDROP FUNCTION dbo.Fun_SplitByLineGO-- =============================================-- Author:yenange-- Create date: 2017-04-26-- Description:按行分割表值函数-- =============================================CREATE FUNCTION dbo.Fun_SplitByLine(@str NVARCHAR(MAX)) RETURNS @t TABLE (rowNum INT,line   NVARCHAR(MAX))ASBEGINDECLARE @i INTSET @i=1WHILE CHARINDEX(CHAR(10),@str)>0BEGININSERT 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+1ENDIF LEN(@str)>0BEGININSERT INTO @t(rowNum,line) VALUES(@i,@str)ENDUPDATE @t SET line=REPLACE(REPLACE(line,char(13),''),CHAR(10),'')RETURN ENDGO/*SELECT * FROM dbo.Fun_SplitByLine('abcd') AS fsrowNumline1a2b3c4d*/