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.