Split string in SQL Server to a maximum length, returning each as a row
sql, sql-server, t-sql
Solution
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.
Problem
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.