Breakdown text into multiple records

Question

Greetings...

I'm working on a SQL Server query where I have to breakdown text stored in Comments column which is of datatype varchar(max) into multiple records.
For instance, if there are 280000 characters in the Comments column then it should be broken down into 5 parts part 1; 1-64750 characters, part 2; 64751 - 129500 characters, part 3; 129501 - 194250 characters, part 4; 194251 - 259000 characters and the last remaining part 4; 259001 - 280000 characters to be broken down into 5 parts part 1; 1-64750 characters, part 2; 64751 - 129500 characters, part 3; 129501 - 194250 characters, part 4; 194251 - 259000 characters and the last remaining part 4; 259001 - 280000 characters. So, the expected output should look like below.

Query:
SQL Query
When I run my query I get only one record with PartId 1 but not the rest. Unable to figure out what I'm missing.
Expected Output:

Id	PartId	CommentPart
1	1	From Character 1 - 64750 characters
1	2	From Character 64751 - 129500 characters
1	3	From Character 129501 - 194250 characters
1	4	From Character 194251 - 259000 characters
1	5	From Character 259001 - 280000 characters

Thank you in advance.

Answer 1

Fix the INSERT statement and re-try your script:

INSERT INTO #CommentsTable (ID, Comments)
VALUES (1, REPLICATE(cast('A' as varchar(max)), 280000));

Also try an alternative:

;
with Q as
(
	select ID, PartID = 1, CommentPart = substring(Comments, 1, @ChunkSize), i = @ChunkSize + 1
	from #CommentsTable
	union all
	select c.ID, PartID + 1, substring(c.Comments, i, @ChunkSize), i + @ChunkSize
	from #CommentsTable c
	inner join Q on Q.ID = c.ID
	where i <= len(c.Comments)
)
select ID, PartID, CommentPart 
from Q
order by ID, PartID
option (maxrecursion 0)