Raymond Lewallen

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

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.

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

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

Thanks - very helpful.

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

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!

This is really perfect for generating a random string.

Thanks a lot for such a nice code.

Just what I needed. Thanks!

Pingback from  WebMart Blog &raquo; SQL Server 2005 function to create a random string

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

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.

Wow... A perfect Procedure for generating Password or PIN.

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