Today i’ll explain how to generate random password in SQL by using a template.
Template work as like as format that every new password will generate as same format.
declare @count int ,@randomChar char(1) ,@password varchar(30) ,@specialChar varchar(50) ,@passwordTemplate varchar(30)=’ABD@dot12′
select @count = 0 ,@password = ” ,@specialChar = ‘!#$%&()*/<=>?@{|}’
set @count = 1 while @count <= len(@passwordTemplate)
begin
set @randomChar = case –upper alpha [A-Z] when ascii(substring(@passwordTemplate, @count, 1)) between 65 and 90 then char(65 + convert(int,floor(rand()*26)))
–lower alpha [a-z]
when ascii(substring(@passwordTemplate, @count, 1)) between 97 and 122 then char(97 + convert(int,floor(rand()*26)))
–number [0-9]
when ascii(substring(@passwordTemplate, @count, 1)) between 48 and 57 then convert(char(1), convert(int,floor(rand() * 10)))
–special (printable) character
else substring(@specialChar, convert(int,floor((rand()*len(@specialChar))+1)), 1)
end
select @count = @count+1 ,@password = @password + @randomChar
end
select @password as Random Password
