Split a long text into table rows


CREATE function dbo.usp_split_long_text
(
    @text nvarchar(max),
    @line_len int
)
returns @table table
(
    Line nvarchar(4000)
)
begin
    declare @nextString nvarchar(max)
    declare @nextLine nvarchar(4000)
    declare @pos int
	declare @pattern nvarchar(100)

	-- Assume pattern would be found with maximum length of characters
	set @pattern = '%[, ]%'

    set @nextString = ''
	set @nextLine = ''

    set @pos = patindex(@pattern, @text)
    while (@pos <> 0)
    begin
        set @nextString = substring(@text, 1, @pos)

		if len(@nextLine + @nextString) > @line_len
		begin
			insert into @table (Line) values	(@nextLine)

			set @nextLine = @nextString
		end
		else
		begin
			set @nextLine = @nextLine + @nextString
		end

        set @text = substring(@text, @pos + 1, len(@text))
        set @pos = patindex(@pattern, @text)
    end

	if len(@nextLine + @text) > 0
	begin
		if len(@nextLine + @text) > @line_len
		begin
			insert into @table (Line) values	(@nextLine)

			insert into @table (Line) values	(@text)
		end
		else
		begin
  		    insert into @table (Line) values	(@nextLine + @text)
		end
	end

    return
end