Random Text Data

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  March 1, 2010 at 11:43 am

  #211750

  Hello Everyone

  I am looking for a way to create some random text data that is over 2000 characters. It can have sentences and punctuation. I am not sure where to begin with this. I know OOP Language can create this, I would think SQL can create this also.

  I am looking thru the BOL, but if you don't know what it is named, how can you look it up.

  Thanks

  Andrew SQLDBA

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  March 1, 2010 at 12:13 pm

  #1126636

  Thanks any way, I found a really great random text generator for SQL. This will create all kinds of junk text,

  Just change the @Length variable for the length of the Text. Remove the blank space from the @ValidCharacters to remove sentences.

  DECLARE @RandomText varchar(8000)

  DECLARE @counter smallint

  DECLARE @RandomNumber float

  DECLARE @RandomNumberInt bigint

  DECLARE @CurrentCharacter varchar(1)

  DECLARE @ValidCharacters varchar(255)

  DECLARE @Length int

  SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789._'

  DECLARE @ValidCharactersLength int

  SET @ValidCharactersLength = len(@ValidCharacters)

  SET @CurrentCharacter = ''

  SET @RandomNumber = 0

  SET @RandomNumberInt = 0

  SET @RandomText = ''

  SET @Length = 5000

  SET NOCOUNT ON

  SET @counter = 1

  WHILE @counter < (@Length + 1)

  BEGIN

  SET @RandomNumber = Rand()

  SET @RandomNumberInt = Convert(BIGINT, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

  SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

  SET @counter = @counter + 1

  SET @RandomText = @RandomText + @CurrentCharacter

  END

  SELECT @RandomText