TSQL Pseudo Random text generator

Question

I am doing some performance testing on a SQL sproc and just want to bang out a quick data generator for testing.

I am after a simple way to generate a pseudo random (true random not needed in this case) varchar field.

Ideas I have so far is having a character definition of valid characters that can be used and then build the string from this definition and use a pseudo random length for length variation with a max/min length defined.

Edit:

My test data generator:

DECLARE @MyDataTable TABLE
(
  RecID int IDENTITY(1,1) PRIMARY KEY,
  SomeText varchar(255)
)

DECLARE @RecId int, @SomeText varchar(255),
        @maxlength int, @minlength int, 
        @RecordCount int, @Counter int
SET @maxlength = 254
SET @minlength = 50
SET @RecordCount = 500000
SET @Counter = 1

WHILE (@Counter < @RecordCount)
BEGIN
 INSERT INTO @MyDataTable
 (
  SomeText
 )
 SELECT  TOP 1
 ( 
   select top (abs(checksum(newid())) % (@maxlength-@minlength) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A'))  
   from sys.all_objects a1
   where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */
   for xml path('')
 ) as NewRandomString 
 FROM sys.all_objects t;
 SET @Counter = @Counter + 1
END

Mimimimic

Answer 1

I wrote a blog post on this recently.

https://lobsterpot.com.au/blog/2009/12/07/randomising-data

select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A')) 
from sys.all_objects 
for xml path('')
;

Edit: Sorry - didn't include the random length thing...

SELECT 
(
  select top (abs(checksum(newid())) % (@maxlength-@minlength) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A')) 
  from sys.all_objects 
  for xml path('')
) as NewRandomString
FROM yourTable; /* Maybe something like dbo.nums? */

Edit: Sorry - needs to be correlated...

SELECT  
( 
  select top (abs(checksum(newid())) % (@maxlength-@minlength) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A'))  
  from sys.all_objects a1
  where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */
  for xml path('')
) as NewRandomString 
,*
FROM sys.all_objects t;

Jael

Sorry for the edits... I figured I'd leave the history there too for you. You may want to add one to allow strings that are @maxlength long: (@maxlength-@minlength+1) – Jael

Thanks Rob, I think it is quite an elegant solution. – Mimimimic

link is now broken - tried searching for an archived version to no avail. @RobFarley – Selfloading

Fixed now Mitch – Jael

Answer 2

For SQL Server 2008

SELECT
    --fixed length
    CAST(CRYPT_GEN_RANDOM(50) AS varchar(100)),
    --variable length
    CAST(CRYPT_GEN_RANDOM(ABS(CHECKSUM(NEWID()))%50) AS varchar(100))

Samples:

r¡Ñ”ã8Ò¯wß×1W=ýÎÜTÜN:Læ*é=Öô/qAtmտ׌1):¢ìèð’¾N
mÁ­BòºÇòWãmßyWßðÛ2ﬔœ¹t ¦2›ÏÀë?î7Ä›››ºªb

My evil twin wants to use this as a password generator...

Scamper

By a gazillion to one shot, you correctly guessed my password! – Selfloading

Answer 3

This will generate a random string of variable length.

DECLARE     @text nvarchar(255),
            @length int,
            @i int;
SET @i = 0
SET @text = ''
SET @length = RAND() * 50 + 215
WHILE (@i < @length)
BEGIN
    SET @text = @text + CHAR(RAND() * 26 + 65)
    SET @i = @i + 1
END

Nickeliferous

Answer 4

If you need it quick or you don't want to do it yourself, you can also use the tool from

http://www.generatedata.com/

but you can only generate 100 rows if you are just using the online demo.

Federica

Answer 5

You can randomize LOREM IPSUM words like this:

SELECT STRING_AGG(text, ' ')
FROM (
    SELECT top(64) text
    FROM (
        /** 
         * 64 words dictionary, you can add more. 
         **/
        VALUES ('lorem'), ('ipsum'), ('dolor'), ('sit'), ('amet,'), ('consectetur'), ('adipiscing'), ('elit,'), ('sed'), ('do'), ('eiusmod'), ('tempor'), ('incididunt'), ('ut'), ('labore'), ('et'), ('dolore'), ('magna'), ('aliqua.'), ('ut'), ('enim'), ('ad'), ('minim'), ('veniam,'), ('quis'), ('nostrud'), ('exercitation'), ('ullamco'), ('laboris'), ('nisi'), ('aliquip'), ('ex'), ('ea'), ('commodo'), ('consequat.'), ('duis'), ('aute'), ('irure'), ('in'), ('reprehenderit'), ('voluptate'), ('velit'), ('esse'), ('cillum'), ('eu'), ('fugiat'), ('nulla'), ('pariatur.'), ('excepteur'), ('sint'), ('occaecat'), ('cupidatat'), ('non'), ('proident,'), ('sunt'), ('culpa'), ('qui'), ('officia'), ('deserunt'), ('mollit'), ('anim'), ('id'), ('est'), ('laborum')
    ) AS a(text)
    ORDER BY NEWID()
) t

This will return a different lorem ipsum string each time you run that query, later on, if you want to update multiple rows you'll have to create a function:

CREATE VIEW getNewID as select newid() as new_id;

CREATE OR ALTER FUNCTION dbo.lorem_ipsum(@length bigint) RETURNS NVARCHAR(max) AS
BEGIN
    DECLARE @return NVARCHAR(MAX)
    SELECT @return = STRING_AGG(text, ' ')
    FROM (
        SELECT top(64) text
        FROM (
            VALUES ('lorem'), ('ipsum'), ('dolor'), ('sit'), ('amet,'), ('consectetur'), ('adipiscing'), ('elit,'), ('sed'), ('do'), ('eiusmod'), ('tempor'), ('incididunt'), ('ut'), ('labore'), ('et'), ('dolore'), ('magna'), ('aliqua.'), ('ut'), ('enim'), ('ad'), ('minim'), ('veniam,'), ('quis'), ('nostrud'), ('exercitation'), ('ullamco'), ('laboris'), ('nisi'), ('aliquip'), ('ex'), ('ea'), ('commodo'), ('consequat.'), ('duis'), ('aute'), ('irure'), ('in'), ('reprehenderit'), ('voluptate'), ('velit'), ('esse'), ('cillum'), ('eu'), ('fugiat'), ('nulla'), ('pariatur.'), ('excepteur'), ('sint'), ('occaecat'), ('cupidatat'), ('non'), ('proident,'), ('sunt'), ('culpa'), ('qui'), ('officia'), ('deserunt'), ('mollit'), ('anim'), ('id'), ('est'), ('laborum')
        ) AS a(text), getNewID
        
        ORDER BY new_id
    ) t
    return upper(substring(@return, 1, 1)) + substring(@return, 2, @length - 1);
END

And then update all your values:

UPDATE my_custom_table
SET my_text_column = dbo.lorem_ipsum(len(my_text_column))

Reamer