Split string in SQL Server to a maximum length, returning each as a row

Question

Is there a way to split a string (from a specific column) to n-number chars without breaking words, with each result in its own row?

Example:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.

Results:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the
PSO department  Customer states terms should be Net 60 not Net 30.
Please review signed contract for this information.

I know I can use charindex to find the last space, but im not sure how i can get the remaining ones and return them as rows.

Answer 1

Try something like this. May be your can create a SQL function of following implementation.

DECLARE @Str VARCHAR(1000)
SET @Str = '2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.'

DECLARE @End INT
DECLARE @Split INT

SET @Split = 100

declare @SomeTable table
(
  Content varchar(3000)
)


WHILE (LEN(@Str) > 0)
BEGIN
    IF (LEN(@Str) > @Split)
    BEGIN
        SET @End = LEN(LEFT(@Str, @Split)) - CHARINDEX(' ', REVERSE(LEFT(@Str, @Split)))
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(LEFT(LEFT(@Str, @Split), @End))))
        SET @Str = SUBSTRING(@Str, @End + 1, LEN(@Str))
    END
    ELSE
    BEGIN
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(@Str)))
        SET @Str = ''
    END
END

SELECT *
FROM @SomeTable

Output will be like this:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the
PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract
for this information.

Answer 2

I read some articles and each of them has error or bad performance or not working in small or big length of chunk we want. You can read my comments even in this article below of any answer. Finally i found a good answer and decided to share it in this question. I didn't check performance in various scenarios but i think is acceptable and working fine for small and big chunk length. This is the code:

CREATE function SplitString
(   
    @str varchar(max),
    @length int
)
RETURNS @Results TABLE( Result varchar(50),Sequence INT ) 
AS
BEGIN

DECLARE @Sequence INT 
SET @Sequence = 1

    DECLARE @s varchar(50)
    WHILE len(@str) > 0
    BEGIN
        SET @s = left(@str, @length)
        INSERT @Results VALUES (@s,@Sequence)

        IF(len(@str)<@length)
        BREAK

        SET @str = right(@str, len(@str) - @length)
        SET @Sequence = @Sequence + 1
    END
    RETURN 
END

and source is @Rhyno answer on this question: TSQL UDF To Split String Every 8 Characters

Hope this help.

Answer 3

Just to see if it could be done, I came up with a solution that doesn't loop. It's based on somebody else's function to split a string based on a delimiter.

Note: This requires that you know the maximum token length ahead of time. The function will stop returning lines upon encountering a token longer than the specified line length. There are probably other bugs lurking as well, so use this code at your own caution.

CREATE FUNCTION SplitLines
(
    @pString    VARCHAR(7999),
    @pLineLen   INT,
    @pDelim     CHAR(1)
)
RETURNS TABLE
   WITH SCHEMABINDING
AS  
RETURN
WITH
      E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4),
lines AS (
  SELECT TOP 1
         1 as LineNumber,
         ltrim(rtrim(SUBSTRING(@pString, 1, N))) as Line,
         N + 1 as start
  FROM cteTally
  WHERE N <= DATALENGTH(@pString) + 1
    AND N <= @pLineLen + 1
    AND SUBSTRING(@pString + @pDelim, N, 1) = @pDelim
  ORDER BY N DESC
UNION ALL
  SELECT LineNumber, Line, start
  FROM (
    SELECT LineNumber + 1 as LineNumber,
           ltrim(rtrim(SUBSTRING(@pString, start, N))) as Line,
           start + N + 1 as start,
           ROW_NUMBER() OVER (ORDER BY N DESC) as r
    FROM cteTally, lines
    WHERE N <= DATALENGTH(@pString) + 1 - start
      AND N <= @pLineLen
      AND SUBSTRING(@pString + @pDelim, start + N, 1) = @pDelim
  ) A
  WHERE r = 1
)
SELECT LineNumber, Line
FROM lines

It's actually quite fast and you can do cool things like join on it. Here's a simple example that gets the first 'line' from every row in a table:

declare @table table (
  id int,
  paragraph varchar(7999)
)
insert into @table values (1, '2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.')
insert into @table values (2, 'Is there a way to split a string (from a specific column) to n-number chars without breaking words, with each result in its own row?')

select t.id, l.LineNumber, l.Line, len(Line)
from @table t
cross apply SplitLines(t.paragraph, 42, ' ') l
where l.LineNumber = 1