title: Split a String into fixed length parts in SQL Server 2008
body: <p>I am working with Split string method, I have done with splitting a string into fixed length chunks, but the issue is, it breaks the word , I want to keep intact the word and on the base of "SPACE" character ,Split down.</p>
  
  <p>Below I am attaching the Function with test result as well.
  Kindly guide or instruct me how to work with.</p>
  
  <pre><code>/*
  Select dbo.SplitFixedLengthString('This is me , I am going to split this    string in such a way that it will not break any word, rather it keeps word',16)
  */
  
  CREATE FUNCTION [dbo].[SplitFixedLengthString]
  (
    @string NVARCHAR(MAX) ,
    @stringlength INT
  )
  RETURNS NVARCHAR(MAX)
  AS 
  BEGIN
      DECLARE @tempStr NVARCHAR(MAX)
      DECLARE @finalString NVARCHAR(MAX)
      IF LEN(@string) &gt; 0
          AND @stringlength &gt; 0 
          BEGIN                
              SELECT  @tempStr = ''                
              DECLARE @i INT 
              SET @i = 1
  
              WHILE @i &lt;= LEN(@string) 
                  BEGIN
                      SELECT  @tempStr = @tempStr + SUBSTRING(@string, @i,@stringlength) + (CHAR(13) + CHAR(10))                        
                      SET @i = @i + @stringlength
                  END
  
          END
  
      RETURN @tempStr
  END
  </code></pre>
  
  <p>Test Result:</p>
  
  <hr>
  
  <pre><code>This is me , I a
  m going to split
  this string in 
  such a way that 
  it will not brea
  k any word, rath
  er it keeps word
  </code></pre>
  
  <p>(1 row(s) affected)
  As we can see the line one of output is splitting the word "am" like wise the line 4,5.</p>
  
  <p>suggest me solution please.</p>
  
  <p><strong>Here are my efforts:</strong></p>
  
  <pre><code>--DECLARE @x VARCHAR(32) = 'xyzxyzyyythgetdghydgsh j';
  --SELECT LEN(@x) - CHARINDEX(' ', REVERSE(@x)) + 1;
  
  
  DECLARE @string NVARCHAR(MAX) 
  SELECT @string = 'This is me , I am going to split this string in such a way that it will not break any word, rather it keeps word' 
  --SELECT @string = 'This is me,I am going to Trim off things in such a way that it will add newline having '    
  DECLARE      @stringlength INT
  SELECT @stringlength = 11
  
  BEGIN
      DECLARE @tempStr NVARCHAR(MAX)
      DECLARE @finalString NVARCHAR(MAX) = ''
      IF LEN(@string) &gt; 0
          AND @stringlength &gt; 0 
          BEGIN                
              SELECT  @tempStr = ''                
              DECLARE @start_index INT = 1
              DECLARE @last_index INT = 0               
              DECLARE @lastindex INT  = LEN(@string)-1                
              WHILE @start_index &lt;  @lastindex
                  BEGIN                                          
                      SELECT  @tempStr  = SUBSTRING(@string, @start_index,@stringlength )                                                          
                      IF RIGHT(@tempStr,1) = ' '                        
                      BEGIN   
  
                          SET @finalString = @finalString + @tempStr + (CHAR(13) + CHAR(10))
                          SET @start_index = @start_index + @stringlength
                      END
                      ELSE
                         BEGIN                                                   
                          --SELECT @last_index = LEN(@tempStr) - CHARINDEX(' ', REVERSE(@tempStr)) + 1;
                          SELECT @last_index  = LEN(@tempStr) - CHARINDEX(' ',REVERSE(@tempStr))                          
                          IF @last_index = 0
                          BEGIN
                              SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)+1))+  (CHAR(13) + CHAR(10))
                              SET @start_index = @start_index + LEN(@tempStr)                             
                          END
                          ELSE
                          BEGIN                            
                              SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,@last_index +1))+  (CHAR(13) + CHAR(10))
                              SET @start_index = @start_index + @last_index                               
                              END                                                   
                      END                        
                      IF @start_index + @stringlength &gt;= @lastindex
                        BEGIN                                                                         
                          SET @finalString = @finalString + LTRIM(SUBSTRING(@string, @start_index,(@lastindex - @start_index)+1))+  (CHAR(13) + CHAR(10))
                          SET @start_index = @start_index + (@lastindex - @start_index)                           
                      END
  
  
                  END
  
          END
  
      SELECT @finalString
  END
  </code></pre>
  
  <p><strong>Here is the output</strong></p>
  
  <pre><code> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This is me 
  , I am 
  going to 
  split 
  this 
  string in 
  such a way 
  that it 
  will not 
  break any 
  word, 
  rather it 
  keeps wor
  
  (1 row(s) affected)
  </code></pre>