Wrap text in SQL server using function

Question

I googled a lot to wrap string with minimum defined length, but I am unable to get any solution. I created my own function that can wrap text by given number of characters per line. This post may help to others 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

Answer 2

I also created a short version for warping the text in T-Sql

CREATE FUNCTION [dbo].[WrapText]
(
    @List NVARCHAR(MAX),
    @length INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result AS NVARCHAR(MAX);

    SELECT @result = CASE
                         WHEN @result IS NULL THEN
                             SUBSTRING(@List, number.Number - @length + 1, @length)
                         ELSE
                             @result + CHAR(13) + CHAR(10) + SUBSTRING(@List, number.Number - @length + 1, @length)
                     END
    FROM
    (
        SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
        FROM sys.all_objects
    ) number
    WHERE number.Number <= LEN(@List) + @length - 1
          AND number.Number % @length = 0;

    RETURN @result;
END;

Calling Function:

SELECT dbo.WrapText('abcdefghijklmnopqrstuvwxyz',5)

If you are coping the result from grid, it will not past with line breaks as explained here https://stackoverflow.com/a/59189881/1606054