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

Question

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

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 I'm not sure how I can get the remaining ones and return them as strings.

Answer 1

Try something like this. Maybe you can create an SQL function of the 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 

The 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

Just to make sure this can be done, I came up with a solution that doesn't work. It is based on some function to split a line based on a separator.

Note: This requires that you know the maximum token length ahead of time. The function will stop returning strings when it encounters a token longer than the specified string length. There are probably other errors, so use this code in your own care.

 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 pretty fast, and you can do cool things, like joining it. Here is a simple example that gets the first row from each row of 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 

Answer 3

I read several articles, and each of them has an error or poor performance or does not work in the small or long length of the piece that we want. You can read my comments even in this article below any answer. Finally, I found a good answer and decided to share it on this issue. I have not tested performance in various scenarios, but I think it is acceptable and works fine for small and long piece lengths. 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 the source is @Rhyno's answer to this question: TSQL UDF for line splitting Every 8 characters

I hope for this help.

Answer 4

I know this is a little late, but a recursive cte will achieve this.

You can also use a visit table that contains a sequence of numbers that will be supplied to the substring as a factor for the initial index.