Wrap text in SQL server using function

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.

  • Stored procedure parameter was not supplied error
  • EF select is not getting record
  • Storing video duration time in sql server
  • Deleting Global Temporary Tables (##tempTable) in SQL Server
  • Querying XML column in SQL Server
  • Counting lines of code of Stored Procedures in SQL Server 2005
  • One Solution collect form web for “Wrap text in SQL server using function”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.