See Also: Main_Page - Transact SQL Code Library - String Manipulation Functions

Display a word-wrapped string

CREATE PROC WORD_WRAPPED_STRING
           @string VARCHAR(800),
           @length INT
AS
  SET NOCOUNT ON
  
  DECLARE  @string_length INT,
           @output        VARCHAR(800),
           @trailing_char CHAR(1),
           @leading_char  CHAR(1)
  
  IF @length = LEN(@string)
    BEGIN
      PRINT 'please provide a smaller length for dividing the string.'
      
      RETURN
    END
  
  IF CHARINDEX(CHAR(13),@string) <> 0
    BEGIN
      SELECT @string = REPLACE(@string,CHAR(10),' ')
      
      SELECT @string = REPLACE(@String,CHAR(13),'')
    END
  
  SET CONCAT_NULL_YIELDS_NULL OFF
  
  SELECT @string_length = LEN(@string)
  
  WHILE @string_length & GT
    ; @length
  
  BEGIN
    SET @trailing_char = SUBSTRING(LEFT(@string,(@length + 1)),(@length + 1),
                                   1)
    
    SET @leading_char = SUBSTRING(LEFT(@string,@length),@length,1)
    
    IF @leading_char = ' '
        OR @trailing_char = ' '
      BEGIN
        SELECT @output = @output + SUBSTRING(@string,1,@length) + CHAR(13)
        
        SELECT @string = SUBSTRING(@string,(@length + 1),(LEN(@string) - @length + 1))
        
        SELECT @string_length = LEN(@string)
      END
    ELSE
      -- find the first occurence of a blank space before the trailing space
      BEGIN
        DECLARE  @i INT
        
        SELECT @i = CHARINDEX(' ',REVERSE(SUBSTRING(@string,1,@length)))
        
        SELECT @output = @output + SUBSTRING(@string,1,(@length - @i)) + CHAR(13)
        
        SELECT @string = SUBSTRING(@string,(@length - @i + 2),(LEN(@string) - (@length - @i)))
        
        SELECT @string_length = LEN(@string)
      END
  END
  
  SELECT @output = @output + CHAR(13) + @string
  
  SELECT @output

GO