Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Updated random password or string generator in T-Sql for Sql Server

Here is an updated version of my random string or password generator
for T-Sql, made into a stored procedure with some other modifications.

The stored procedure

/***************************************************************************
* Created By: Raymond Lewallen
* Date:  05/16/2005
* Purpose: Generate a random string of given length
*
* Comments: Everything is self-explanatory.  Right now
*  max length is set to 100. So anything between 1
*  and 100 will work for a length.
*
*   If you specify a @charactersToUse,
*  the bit flags get ignored.
*
*   All spaces are stripped from the
*  @charactersToUse.
*
*   Characters can repeat. Will be
*  handled in a future version.
***************************************************************************/

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 @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

And here are some examples of how to use it.

Use the bit flags – numbers only

declare @a varchar(20)
exec dbo.GenerateRandomString 1,0,0,null,20,@a OUT
print @a

Output: 44509955862560034316

Use the bit flags – entire character set

declare @a varchar(30)
exec dbo.GenerateRandomString 1,1,1,null,30,@a OUT
print @a

Output: bl4Dut6ACchq460u65j2fkYoalrAAN

Use a simple custom character set

declare @a varchar(50)
exec dbo.GenerateRandomString 1,1,1,’Ab’,50,@a OUT
print @a

Output: bbbAAAbbbbAbbbAbbAAAbAbbbbbAbAbAbbAAAbAAbAbAbbAbbb

Use a more complex character set

declare @a varchar(50)
exec dbo.GenerateRandomString 1,1,1,’ManagedCodeRocksMyWorld’,50,@a OUT
print @a

Output: nednsndgcdgdnrgolRWRsgooRsdkMoCMRWedyglaknooodrdRd

This entry was posted in Most Popular, Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

27 Responses to Updated random password or string generator in T-Sql for Sql Server

  1. schlub says:

    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 :)

  2. Muddsair Sharif says:

    thanks… i have got ;what in my mind. thanks for saving my lot of time.

  3. Juan Valera says:

    Excelent post . Thanks Dude!

  4. Alex says:

    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?

  5. HJ says:

    Thank you. Great procedure.

  6. JAV! says:

    Thanks a lot. Great job!!

  7. Anil Gupta says:

    Thanks a lot.
    you saved me lot of time.

  8. Rajesh Kalathilayil says:

    Thank you very much!

  9. Ashwini says:

    Thanks for giving me nice solution

  10. Marius Smith says:

    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

  11. Chandra Mouli says:

    Wow… A perfect Procedure for generating Password or PIN.

  12. Alex says:

    Nice work! Thanks.

  13. matt says:

    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.

  14. Jens Peter Winkler says:

    Very useful, thanks a lot. I have used the procedure for obfuscating personal data.

  15. Jason says:

    Just what I needed. Thanks!

  16. Atul says:

    This is really perfect for generating a random string.
    Thanks a lot for such a nice code.

  17. Kathryn says:

    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!

  18. Chris Beck says:

    Thanks – very helpful.

    One defect: the application of “use upper case” and “use lower case” are reversed.

  19. komp says:

    Excellent work. I appreciate it. It is the one which I am looking for. Saved me lots of time. Thanks

  20. Carl says:

    Many thanks, saved lots of work. Flexible yet simple – great!

  21. Steve says:

    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.

  22. Wes F. says:

    Thank you so much for this great procedure! Exactly what I was looking for!

  23. K Sub says:

    Thank you. I found it very useful.

  24. tribune says:

    awesome procedure! cheers

  25. Raul says:

    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

  26. rlewallen says:

    Glad to hear it helped you out Ben :)

  27. Ben says:

    Hey Raymond,

    Thanks for the post. Your code came in quite handy and was a great help to me as a newby.

    Ben

Leave a Reply