Here is another version that gives a sentence instead of just a single word per row, as requested by Raj More.
;
declare
@Lorem nvarchar(max),
@SentenceToGen int,
@WordsPerSentence int,
@Factor int
select
@Lorem = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.',
@SentenceToGen = 200,
@WordsPerSentence = 10
-- strip punctuations
set @Lorem = replace(@Lorem, ',', '')
set @Lorem = replace(@Lorem, '.', '')
;
with
Num1(Pos) as -- number of chars in @Lorem
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num1 where Pos < len(@Lorem)
),
Words as
(
select lower(substring(@Lorem, Pos, charindex(' ', @Lorem + ' ', Pos) - Pos)) as Word
from Num1 where Pos <= len(@Lorem) and substring(',' + @Lorem, Pos, 1) = ' '
),
WordsCnt(Factor) as
(
select ceiling(cast(@SentenceToGen * @WordsPerSentence as float) / count(*)) from Words
),
Num2(Pos) as -- product of words required, to be divided by number of words found in @Lorem
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num2 cross join WordsCnt where Pos < WordsCnt.Factor
),
Sentences as
(
select
ntile(@SentenceToGen) over (order by newid()) as SentenceId,
Word
from
Num2
cross join
Words
),
Num3(Pos) as -- list of SentenceId
(
select distinct SentenceId from Sentences
)
select
(
select top (@WordsPerSentence)
Word + ' '
from
Sentences
where
Sentences.SentenceId = Num3.Pos
for xml path('')
)
as Sentence
from
Num3
option (maxrecursion 0)