Found it. Again, sorry for the delay.
First, you need a Tally table which is nothing more than a table with a single column of sequential numbers. For more information on how a Tally table works to replace a loop, please see the following article...
Here's the code to build a "standard" 11,000 row Tally table but do read the article above to really understand what it does...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 IDENTITY(INT,1,1) AS N
FROM Master.dbo.SysColumns sc1,
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Here's the code you need for your 25 character sentences. You could paramaterize the "25" in the code to make a User Defined Function that would have some bit a flexability...
--===== Declare a variable to hold a long string.
-- This would likely be a parameter in a User Defined Function
DECLARE @pLongString VARCHAR(8000);
SELECT @pLongString = 'This is a long sentence and I want to split it into multiple sentences of 25 character each with out splitting a word into multiple lines.';
--===== We need a place to hold split words and determine which line of 25 characters each belongs to.
DECLARE @Return TABLE
WordNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
--====== cteSplit splits all of the words and their trailing spaces and stores them in a table variable
SELECT Word = SUBSTRING(@pLongString,t.N,CHARINDEX(' ',@pLongString+' ',t.N)-t.N+1)
FROM dbo.Tally t
WHERE N <= LEN(@pLongString)+1
AND SUBSTRING(' '+@pLongString,t.N,1) = ' '
INSERT INTO @Return
--===== Declare some obviously named variables and preset them for use
DECLARE @Width INT,
SELECT @Width = 0,
@Line = 1;
--===== This counts up the length of each word and assigns them to groups of 25 characters
-- which will eventually become the 25 character "sentences". The method used is
-- known as the "Quirky Update".
SET @Width = @Width + LEN(Word),
@Line = Line = CASE WHEN @Width > 25 THEN @Line + 1 ELSE @Line END,
@Width = CASE WHEN @Width > 25 THEN LEN(Word) ELSE @Width END,
@Dummy = WordNum
OPTION (MAXDOP 1);
--===== Concatenate all the words for each numbered sentence.
-- This would be the return for a User Defined Function.
FROM @return t2
WHERE t2.Line = t1.Line --Correlation here
ORDER BY t2.Line,t2.WordNum
FOR XML PATH('')
) AS SomeLetters2
FROM @return t1
GROUP BY t1.Line -- without GROUP BY multiple rows are returned
ORDER BY t1.Line -- Remove this line for a User Defined Function
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)