Wrap text in SQL server using function

Question

I google a lot to wrap string with minimum defined length but I unable to find any solution so I created my own function which can wrap text by given number of characters per line. This post may help to those guys who are looking for same.

Answer 1

Function 1

Create FUNCTION [dbo].[fn_BraekTextInLines]
(
    -- Add the parameters for the function here
    @InString varchar(max), 
    @LineLength int
)
RETURNS nvarchar(max)
AS
BEGIN


if @LineLength <=0 or @LineLength> LEN(@InString)
return @InString

declare @tmp varchar(max)
declare @result varchar(max)
DECLARE @word varchar (max);
declare @addedInResult bit
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT s FROM SplitMax(@InString,'');

OPEN c;

FETCH NEXT FROM c INTO @word;
--set @tmp =@word
WHILE @@FETCH_STATUS = 0
BEGIN


  if LEN(@tmp + ' ' + @word) < @LineLength
  begin
    set @tmp = @tmp + ' ' + @word   
    set @addedInResult = 0
  end
  else
  begin

    set @result =  isnull(@result, ' ') + CHAR(13) +  RTRIM(LTRIM( @tmp))
    set @tmp = @word
    set @addedInResult = 1

  end

    FETCH NEXT FROM c INTO @word;

    if @@FETCH_STATUS <> 0
    begin
     set @result =  isnull(@result, ' ') + CHAR(13) + RTRIM(LTRIM( @tmp))
     set @addedInResult = 1
    end
END

CLOSE c;
DEALLOCATE c;

if @addedInResult=0
begin
    set @result =  isnull(@result, ' ') + CHAR(13) +  RTRIM(LTRIM( @tmp))
end

return @result

END

Function 2

   Create FUNCTION [dbo].[fn_WrapString]
(
    -- Add the parameters for the function here
    @InString varchar(max), 
    @LineLength int
)
RETURNS nvarchar(max)
AS
BEGIN

declare @result varchar(max)
declare @tmp varchar(max)
DECLARE @Line varchar (max);

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT s FROM SplitMax(@InString,CHAR(13));

OPEN c;

FETCH NEXT FROM c INTO @Line;

WHILE @@FETCH_STATUS = 0
BEGIN

  set @tmp = dbo.fn_BraekTextInLines(@Line,@LineLength)
  set @result = isnull(@result,' ') + @tmp

  FETCH NEXT FROM c INTO @Line;
END

CLOSE c;
DEALLOCATE c;

return Rtrim(Ltrim(@result))

END

Function 3

ALTER FUNCTION [dbo].[SplitMax](@String VARCHAR(max), @Delimiter CHAR(1))       
RETURNS @temptable TABLE (s VARCHAR(max))       
AS       
BEGIN       
DECLARE @idx INT       
DECLARE @slice VARCHAR(max)        
SELECT @idx = 1       
IF len(@String)<1 OR @String IS NULL  RETURN       
while @idx!= 0       
BEGIN       
SET @idx = charindex(@Delimiter,@String)       
IF @idx!=0       
SET @slice = LEFT(@String,@idx - 1)       
ELSE       
SET @slice = @String       
IF(len(@slice)>0)  
INSERT INTO @temptable(s) VALUES(@slice)       
SET @String = RIGHT(@String,len(@String) - @idx)       
IF len(@String) = 0 break       
END   
RETURN       
END

Calling Function fn_WrapString to wrap the text

declare @name varchar(max)
set @name = 'Ine was King of Wessex from 688 to 726. He was'+ CHAR(13) +'unable to retain the territorial gains of his predecessor, Cædwalla, who had brought much of southern England under his'
print dbo.fn_WrapString(@name,60)

Output :

Ine was King of Wessex from 688 to 726. He was 
unable to retain the territorial gains of his predecessor,
Cædwalla, who had brought much of southern England under
his