Recently I needed to recreate a large number of 8 character alpha-numeric access codes in a customers database as users where confusing certain characters i.e. 1/l and 0/O.
I wanted to avoiding doing a code deployment to change the access codes so instead of writing C# to update them I decided to use sql. I wrote a simple but effective script that I tested locally before running on the production database.
You can put whatever characters you like in the @characters string and the function will randomly create an access code using just those characters.
begin
declare @accessCode varchar(100)
declare @accessCodeLength as smallint = 8
declare @characters varchar(50)
declare @count int
set @characters = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'
set @count = 0
set @accessCode = ''
while @count < @accessCodeLength
begin
set @accessCode = @accessCode + SUBSTRING(@characters,CAST(CEILING(RAND()*LEN(@characters)) as int),1)
set @count = @count + 1
end
print @accessCode
end
To update table values with the new access code you would need to wrap the above code in a user defined function and call this from an update statement.
