codeMonkey

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