Set Random letters in Uppercase and Lowercase

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set Random letters in Uppercase and Lowercase

Author

Topic

pdude1978
Starting Member

3 Posts

Posted - 2011-03-21 : 12:23:57

Hello Everyone

I'm trying to write a function that would generation a random string from A-Z, it needs to include letters in uppercase and lowercase each time for e.g. ‘DgJkMc’.

I have come across the following code that works perfectly giving random letters each time but not in the format I need.

Any help would be appreciated

Thanks Guys

DECLARE @length int = 5
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
DECLARE @ValidCharactersLength int

SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''
SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN
SET @RandomNumber = Rand()
SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
SET @counter = @counter + 1
SET @RandomID = @RandomID + @CurrentCharacter
END

SELECT @RandomID AS 'ActivationCode'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-21 : 12:57:05

declare @password varchar(8)
set @password=''

select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and (number between 65 and 90 or number between 97 and 122 or number between 48 and 57)
order by newid()
) as t
select @password

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 12:58:18

Does it need to be uniquely Random?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

pdude1978
Starting Member

3 Posts

Posted - 2011-03-21 : 17:53:34

Hi Bret

Yes it does e.g DgSp2. Each time when the function is executed it will generate another unique string

thanks

quote:
Originally posted by X002548

Does it need to be uniquely Random?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-22 : 04:17:43

Have you tied jim's code?

Madhivanan

Failing to plan is Planning to fail

pdude1978
Starting Member

3 Posts

Posted - 2011-03-22 : 06:52:45

Hi Jim

thanks for your help it works perfectly

quote:
Originally posted by jimf

declare @password varchar(8)
set @password=''

select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and (number between 65 and 90 or number between 97 and 122 or number between 48 and 57)
order by newid()
) as t
select @password

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources