split - Resplit long strings in TSQL -

i have table long strings splitted 200char per row. need re-split them max 80char per row, possibile tsql function or have write , external program?

this example (text field lenght reduced have more rows per id)

+----+----------------------------+ | id | textlong                   | +----+----------------------------+ | 1  | long text chunked part 1/3 | +----+----------------------------+ | 1  | long text chunked part 2/3 | +----+----------------------------+ | 1  | long text chunked part 3/3 | +----+----------------------------+ | 2  | long text chunked part 1/2 | +----+----------------------------+ | 2  | long text chunked part 2/2 | +----+----------------------------+ 

to

+----+------------------------+ | id | textsmall              | +----+------------------------+ | 1  | long text chunked      | +----+------------------------+ | 1  |  part 1/3long text     | +----+------------------------+ | 1  | chunked part 2/3 long  | +----+------------------------+ | 1  |  text chunked part 3/3 | +----+------------------------+ | 2  | long text chunked      | +----+------------------------+ | 2  | part 1/2 long text     | +----+------------------------+ | 2  | chunked part 2/2       | +----+------------------------+ 

this solution:

create function [dbo].[resplit] ( @id int, @len int) returns  @returnlist table (id int identity(1,1), txt nvarchar(max)) begin  if @id null     return   declare @fulltxt nvarchar(max)=''  declare @rowlist table (id int identity(1,1) primary key, txt nvarchar(max), processed int default 0)  declare @name nvarchar(255), @value nvarchar(255), @tmp nvarchar(255)  declare @pos int  insert @rowlist(txt) select ltrim(rtrim(nota)) sourcetable id=@id  if (select count(*) @rowlist) = 0     return  while (select count(*) @rowlist processed = 0) > 0     begin         select top 1 @tmp = txt, @pos=id @rowlist processed = 0 order id         set @fulltxt=@fulltxt+isnull(@tmp,'')         update @rowlist set processed = 1 id = @pos      end   while len(@fulltxt)>0     begin         set @pos=@len         set @tmp = substring(@fulltxt, 0, @pos)         insert @returnlist(txt) select @tmp         if len(@fulltxt)-@pos<=0             set @fulltxt=''         else             set @fulltxt=substring(@fulltxt, @pos+1, len(@fulltxt)-@pos)     end   return end 

short explanation:

• implode rows 1 string nvarchar(max)

• split string rows of defined size