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
QlACUJc2uHQ1DkX2jbjnpcyPD1d8xEhDZm2XyRrhTE77HRToHope 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 @randomStringWritten by Casey
Related protips
1 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
endend
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
