views:

591

answers:

7

I have several content tables that I want to fill up with random paragraphs of text. In MS Word, I can simply put =rand() and presto! I get three paragraphs of fresh-off-the-press text.

Is there a SQL script/command that I can use to generate random dictionary words using t-sql?

A: 

No, but why don't you use MS Word to create a list of words first and then insert those into the database?

Andrew Hare
This is a _content_ data set with hundreds of rows in multiple tables. I cannot copy paste from word for all of these rows.
Raj More
Also, this is something that I would reuse as filler on every blank content row created.
Raj More
Import the data in whatever file format you wish using the Tasks-->Import Data feature when right clicking on the database...
Dining Philanderer
+1  A: 

There's nothing built in, but it's easy to do:

create table DictionaryWords 
(
    Id int primary key identity (1,1),
    Word nvarchar(100) not null
)
go

insert DictionaryWords values ('the')
insert DictionaryWords values ('quick')
insert DictionaryWords values ('brown')
insert DictionaryWords values ('fox')
insert DictionaryWords values ('jumped')
insert DictionaryWords values ('over')
insert DictionaryWords values ('the')
insert DictionaryWords values ('lazy')
insert DictionaryWords values ('dog')
go

create procedure dbo.CreateRandomText(@numWords int, @text nvarchar(max) out)
as
begin

    declare @rowcount int
    select @rowcount = count(*) from DictionaryWords

    select @text = ''

    while @numWords <> 0
     select @text = @text + ' ' + Word, @numWords = @numWords - 1
     from DictionaryWords
     where Id = cast(@rowcount * rand() as integer)

end
go

declare @text nvarchar(max)
exec CreateRandomText 10, @text out
select @text
Ben M
A: 

Word pulls from an existing built in dictionary does it not? What set of words are you going to pull from to populate your table?

Off the cuff I would say to import (using xml/Excel file?) data consisting of the words you wish to choose from into a new table then randomly pull off that new table. There has to be an existing XML file out in 'the wild' you can download...

You could make the column an XML data type and retrieve from that file or make it strictly one word per row...

Have fun...

Dining Philanderer
A: 

I don't know about the Word source, but you can also use the Lorem Ipsum generator to generate random text.

GalacticCowboy
+1  A: 

How about using a tool: Test data generator for MS SQL Server databases

AlexKuznetsov
+4  A: 

;
declare 
    @Lorem nvarchar(max),
    @RowsToGen 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.',
    @RowsToGen = 200

-- strip punctuations
set @Lorem = replace(@Lorem, ',', '')
set @Lorem = replace(@Lorem, '.', '')

;
with
Num1(Pos) as
(
    select cast(1 as int)
    union all 
    select cast(Pos + 1 as int) from Num1 where Pos < len(@Lorem)
),
Words as
(
    select substring(@Lorem, Pos, charindex(' ', @Lorem + ' ', Pos) - Pos) as Word
     from Num1 where Pos <= len(@Lorem) and substring(',' + @Lorem, Pos, 1) = ' '
),
WordsCnt(Factor) as
(
    select @RowsToGen / count(*) + 1 from Words
),
Num2(Pos) as
(
    select cast(1 as int)
    union all 
    select cast(Pos + 1 as int) from Num2 cross join WordsCnt where Pos < WordsCnt. Factor
)
select top (@RowsToGen) 
    Word 
from 
    Num2 
cross join 
    Words
order by newid()
option (maxrecursion 0)  
Irawan Soetomo
+1 for using "order by newid()" for randomization.
Ben M
I like this. How do I pivot it for, say, 20 words per line instead of one word per line?
Raj More
@Raj More, sorry was away, please find the 'sentence' version at the bottom.
Irawan Soetomo
+1  A: 

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)         
Irawan Soetomo