Create a random string with fixed characters using SQL

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.