Your @useUpperCase and @useLowerCase are the wrong way around. i.e. When @useUpperCase = 1 it should load the uppercase characters, not the lowercase ones… easily fixed though
Thanks Raymond. I changed your code to allow for the removal of duplicates to use for myself .
Could your verify this is what you meant as my home language is not English?
Below is the code (changes is at the end where you were setting the password?)
CREATE procedure dbo.GenerateRandomString (
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@charactersToUse as varchar(100),
@passwordLength as smallint,
@password varchar(100) OUT
)
As
Begin
if @passwordLength < = 0
raiserror('Cannot generate a random string of zero length.',16,1)
declare @characters varchar(100)
declare @count int
set @characters = ''
if @useNumbers = 1
begin
-- load up numbers 0 - 9
set @count = 48
while @count <=57
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
if @useLowerCase = 1
begin
-- load up uppercase letters A - Z
set @count = 65
while @count <=90
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
if @useUpperCase = 1
begin
-- load up lowercase letters a - z
set @count = 97
while @count <=122
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
set @count = 0
set @password = ''
-- If you specify a character set to use, the bit flags get ignored.
if Len(@charactersToUse) > 0
begin
while charindex(@charactersToUse,’ ‘) > 0
begin
set @charactersToUse = replace(@charactersToUse,’ ‘,”)
end
if Len(@charactersToUse) = 0
raiserror(‘Cannot use an empty character set.’,16,1)
while @count <= @passwordLength
begin
set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
set @charactersToUse = left(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)) + right(@charactersToUse,LEN(@charactersToUse)- CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+2)
set @count = @count + 1
end
end
else
begin
while @count <= @passwordLength
begin
set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
set @count = @count + 1
end
end
I noticed your Upper/Lower case checks are reversed (the if @useUpperCase = 1 generates the lower case characters, etc). Also, the @count should start at 1 instead of zero when creating the string, otherwise the resulting string is 1 character longer than what you requested. It doesn’t show up in you examples because the string length is explicit.
Thanks – this is exactly what I was looking for as well. I did find a bug in the procedure, however. Whatever length you pass in will return a string 1 character longer. Bot “while @count <= @passwordLength” lines in the procedure need to simply use “<” since @count is initialized to 0.
Raymond
Thank you to provide your storage procedure..
Raymond I need to generate a ramdom string or number after a user submits its password using a text box on a web page. I believe your store procedure is the right solution in this case.
I also would like to provide back the password using email in case the user forgot his/her password, would you provide me some ideas how can I convert back the ramdom number generated using your store procedure to the original string the user submited.
Your @useUpperCase and @useLowerCase are the wrong way around. i.e. When @useUpperCase = 1 it should load the uppercase characters, not the lowercase ones… easily fixed though
thanks… i have got ;what in my mind. thanks for saving my lot of time.
Excelent post . Thanks Dude!
How can I use it to update 2000 users, I want to set the wed_id as the random alphanumeric.
could you please help me?
Thank you. Great procedure.
Thanks a lot. Great job!!
Thanks a lot.
you saved me lot of time.
Thank you very much!
Thanks for giving me nice solution
Thanks Raymond. I changed your code to allow for the removal of duplicates to use for myself .
Could your verify this is what you meant as my home language is not English?
Below is the code (changes is at the end where you were setting the password?)
CREATE procedure dbo.GenerateRandomString (
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@charactersToUse as varchar(100),
@passwordLength as smallint,
@password varchar(100) OUT
)
As
Begin
if @passwordLength < = 0
raiserror('Cannot generate a random string of zero length.',16,1)
declare @characters varchar(100)
declare @count int
set @characters = ''
if @useNumbers = 1
begin
-- load up numbers 0 - 9
set @count = 48
while @count <=57
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
if @useLowerCase = 1
begin
-- load up uppercase letters A - Z
set @count = 65
while @count <=90
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
if @useUpperCase = 1
begin
-- load up lowercase letters a - z
set @count = 97
while @count <=122
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
end
set @count = 0
set @password = ''
-- If you specify a character set to use, the bit flags get ignored.
if Len(@charactersToUse) > 0
begin
while charindex(@charactersToUse,’ ‘) > 0
begin
set @charactersToUse = replace(@charactersToUse,’ ‘,”)
end
if Len(@charactersToUse) = 0
raiserror(‘Cannot use an empty character set.’,16,1)
while @count <= @passwordLength
begin
set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
set @charactersToUse = left(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)) + right(@charactersToUse,LEN(@charactersToUse)- CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+2)
set @count = @count + 1
end
end
else
begin
while @count <= @passwordLength
begin
set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
set @count = @count + 1
end
end
end
GO
Wow… A perfect Procedure for generating Password or PIN.
Nice work! Thanks.
Excellent job with this procedure.. very useful!
I noticed your Upper/Lower case checks are reversed (the if @useUpperCase = 1 generates the lower case characters, etc). Also, the @count should start at 1 instead of zero when creating the string, otherwise the resulting string is 1 character longer than what you requested. It doesn’t show up in you examples because the string length is explicit.
Very useful, thanks a lot. I have used the procedure for obfuscating personal data.
Just what I needed. Thanks!
This is really perfect for generating a random string.
Thanks a lot for such a nice code.
Two years on and this post is still a useful resource for people. Thanks for helping to make my task today just that little bit easier!
Thanks – very helpful.
One defect: the application of “use upper case” and “use lower case” are reversed.
Excellent work. I appreciate it. It is the one which I am looking for. Saved me lots of time. Thanks
Many thanks, saved lots of work. Flexible yet simple – great!
Thanks – this is exactly what I was looking for as well. I did find a bug in the procedure, however. Whatever length you pass in will return a string 1 character longer. Bot “while @count <= @passwordLength” lines in the procedure need to simply use “<” since @count is initialized to 0.
Thank you so much for this great procedure! Exactly what I was looking for!
Thank you. I found it very useful.
awesome procedure! cheers
Raymond
Thank you to provide your storage procedure..
Raymond I need to generate a ramdom string or number after a user submits its password using a text box on a web page. I believe your store procedure is the right solution in this case.
I also would like to provide back the password using email in case the user forgot his/her password, would you provide me some ideas how can I convert back the ramdom number generated using your store procedure to the original string the user submited.
I will appreciate any help.
Raul
Glad to hear it helped you out Ben
Hey Raymond,
Thanks for the post. Your code came in quite handy and was a great help to me as a newby.
Ben