Generating random strings with T-SQL

Question

If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?

Answer 1

I came across this blog post first, then came up with the following stored procedure for this that I'm using on a current project (sorry for the weird formatting):

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

Answer 2

I did this in SQL 2000 by creating a table that had characters I wanted to use, creating a view that selects characters from that table ordering by newid(), and then selecting the top 1 character from that view.

CREATE VIEW dbo.vwCodeCharRandom
AS
SELECT TOP 100 PERCENT 
    CodeChar
FROM dbo.tblCharacter
ORDER BY 
    NEWID()

...

SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom

Then you can simply pull characters from the view and concatenate them as needed.

EDIT: Inspired by Stephan's response...

select top 1 RandomChar from tblRandomCharacters order by newid()

No need for a view (in fact I'm not sure why I did that - the code's from several years back). You can still specify the characters you want to use in the table.

Answer 3

You can do it aswell with the following line:

select substring(hashbytes('MD5', rand(convert(varbinary, newid()))), 1, 6) as randomstring

Downside is that the word has a maximum of 32 characters and only uses 0-9 and a-f.

EDIT: yes you're right, corrected.

Answer 4

Using a guid

SELECT @randomString = CONVERT(varchar(255), NEWID())

very short ...

Answer 5

Similar to the first example, but with more flexibility:

-- min length = 8, max length = 12
SET @Length = RAND() * 4 + 8    

-- define allowable character explicitly - easy to read this way an easy to 
-- omit easily confused chars like l (ell) and 1 (one) or 0 (zero) and O (oh)
SET @CharPool = 
    'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
SET @PoolLength = DataLength(@CharPool)

SET @LoopCount = 0
SET @RandomString = ''

WHILE (@LoopCount < @Length) BEGIN
    SELECT @RandomString = @RandomString + 
        SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength), 1)
    SELECT @LoopCount = @LoopCount + 1
END

I forgot to mention one of the other features that makes this more flexible. By repeating blocks of characters in @CharPool, you can increase the weighting on certain characters so that they are more likely to be chosen.

Answer 6

When generating random data, specially for test, it is very usefull to make the data random, but reproductible. The secret is to use explicit seeds for the random funciton, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a funciton that generates object names in a reproducible manner:

create procedure usp_generateIdentifier
    @minLen int = 1
    , @maxLen int = 256
    , @seed int output
    , @string varchar(8000) output
as
begin
    set nocount on;
    declare @length int;
    declare @alpha varchar(8000)
     , @digit varchar(8000)
     , @specials varchar(8000)
     , @first varchar(8000)

    select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
     , @digit = '1234567890'
     , @specials = '_@# '
    select @first = @alpha + '_@';

    select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
     , @seed = (rand(@seed+1)*2147483647);

    declare @dice int;
    select @dice = rand(@seed) * len(@first),
     @seed = (rand(@seed+1)*2147483647);
    select @string = substring(@first, @dice, 1);

    while 0 < @length 
    begin
     select @dice = rand(@seed) * 100
      , @seed = (rand(@seed+1)*2147483647);
     if (@dice < 10) -- 10% special chars
     begin
      select @dice = rand(@seed) * len(@specials)
       , @seed = (rand(@seed+1)*2147483647);
      select @string = @string + substring(@specials, @dice, 1);
     end
     else if (@dice < 10+10) -- 10% digits
     begin
      select @dice = rand(@seed) * len(@digit)
       , @seed = (rand(@seed+1)*2147483647);
      select @string = @string + substring(@digit, @dice, 1);
     end
     else -- rest 80% alpha
     begin
      select @dice = rand(@seed) * len(@alpha)
       , @seed = (rand(@seed+1)*2147483647);
      select @string = @string + substring(@alpha, @dice, 1);
     end

     select @length = @length - 1; 
    end
end
go

When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:

declare @seed int;
declare @string varchar(256);

select @seed = 1234; -- saved start seed

exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;