Last Updated: February 25, 2016
·
5.333K
· caseydunham

T-SQL random alpha-numeric strings using crypt_gen_random

I needed to find a way of producing alpha numeric strings from T-SQL that were not just a UUID or hash of one. I also wanted it to use T-SQL's cryptgenrandom function.

The following code produces character sequences like the following:

KKTRpGPOqFOwr8S9pmkRwpiRRTBVJvy32VkpSWYVvyfoYadW
8LHEKokwrvVBQQJ9P3MCzYtcMPvEbFvEmHtklKjogEUJfmxB
QlACUJc2uHQ1DkX2jbjnpcyPD1d8xEhDZm2XyRrhTE77HRTo

Hope someone else finds this useful

declare @sLength tinyint
declare @randomString varchar(50)
declare @counter tinyint
declare @nextChar char(1)
declare @rnd as float

set @sLength = 48
set @counter = 1
set @randomString = ''

while @counter <= @sLength
begin
    -- crypt_gen_random produces a random number. We need a random    
    -- float.
    select @rnd = cast(cast(cast(crypt_gen_random(2) AS int) AS float) /    
         65535  as float)  
    select @nextChar = char(48 + convert(int, (122-48+1) * @rnd))
    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
 select @randomString

1 Response
Add your response

thanks for this - I needed to generate 125,000 UPPERCASE unique codes with no 1,0,O, or I characters, I used your code and wrapped it in a loop - here's a modified code snipped to create a temp table variable and load 125,00 codes:

declare @sLength tinyint
declare @randomString varchar(50)
declare @counter tinyint
declare @nextChar char(1)
declare @rnd as float

set @sLength = 8
set @counter = 1
set @randomString = ''

declare @table table
(
lineID int identity
,code nvarchar(50)
)

declare @loopCount int
set @loopCount=0
while @loopCount<125000
begin
while @counter <= @sLength
begin
-- cryptgenrandom produces a random number. We need a random

-- float.
select @rnd = cast(cast(cast(cryptgenrandom(2) AS int) AS float) /

65535 as float)

select @nextChar = char(48 + convert(int, (122-48+1) * @rnd))
if ascii(@nextChar)<97 and ascii(@nextChar) not in (0,1,58,59,60,61,62,63,64,91,92,93,94,95,96,73,79,48,49)
begin
select @randomString = @randomString + @nextChar

    set @counter = @counter + 1
end

end
insert into @table values( @randomString)
set @loopCount=@loopCount+1
set @sLength = 8
set @counter = 1
set @randomString = ''
end

select distinct(code),lineID from @table order by lineID

over 1 year ago ·