Password & E-mail Creation via Transact-SQL

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

SQL Server 2000 Forums

Transact-SQL (2000)

Password & E-mail Creation via Transact-SQL

Author

Topic

cthompson
Starting Member

11 Posts

Posted - 2005-02-28 : 10:27:26

I have been requested to create a transact-sql script that generates passwords and e-mails.

The information that i have been given in terms of what is needed is:
------------------------------------------------------------------
Passwords

Current max length of 6 characters.

4 random letters & 2 random numbers.

The numbers can be from 2 – 9

The letters can be: A B C D E F H J K M N R S T U V W X Y Z

No numbers that are: 1 or 0

No Letters that are: G I L O P Q

Usernames

The username will be a maximum of 10 characters with a minimum of 6 characters.

The 1st 9 characters will be made up from the surname and then the last character will be the 1st letter of they initials.

Any username with surname and initials that don’t make up to 6 characters will then have random numbers added to make up 6 characters.

Any duplicates will go into the next phase of the process
(For example can be surname with 2 initials instead of 1 initial and so forth)
-------------------------------------------------------------------

Can anyone give me any idea how this can be done.

Thanks in advance for any ideas.

Regards

Chris

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-02-28 : 11:49:43

Search here for the ORDER BY NEWID phrase (for randomness)...in conjunction with a "SELECT * FROM alphabettable"...which you can make to hold the eligible letters.
After that a bit of "substring" and the "concat" operator "&" will get you close.

Posting same input data and valid expected results would help.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-02-28 : 14:43:33

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44757

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-28 : 15:01:57

This will generate a random password with 4 random letters and two random numbers with numbers and letters in random locations, for a total of 6 characters from your allowed password characters



declare @pc table 
(
PCHAR	char(1) not null,
SEQ	int 	not null	identity(1,1)
)

insert into @pc (PCHAR)
select top 100 percent
	PCHAR
from
(
select 
	PCHAR = substring(STRING,(NUM%len(STRING))+1,1),
	seq1	= newid()
from
	(
	select
		STRING= 'ABCDEFHJKMNRSTUVWXYZ'
	) A
	cross join
	(
	select
		num = abs(convert(int,convert(varbinary(60),NI)))
	from
		(
		select NI = newid() union all
		select NI = newid() union all
		select NI = newid() union all
		select NI = newid()
		) bb
	) b
union all
select 
	PCHAR = substring(STRING,(NUM%len(STRING))+1,1),
	seq1	= newid()
from
	(
	select
		STRING = '23456789'
	) A
	cross join
	(
	select
		num = abs(convert(int,convert(varbinary(60),NI)))
	from
		(
		select NI = newid() union all
		select NI = newid()
		) bb
	) b
) c
ORDER BY
	seq1

select
	NEW_PASSWORD = 
	( select PCHAR FROM @pc WHERE SEQ = 1 )+
	( select PCHAR FROM @pc WHERE SEQ = 2 )+
	( select PCHAR FROM @pc WHERE SEQ = 3 )+
	( select PCHAR FROM @pc WHERE SEQ = 4 )+
	( select PCHAR FROM @pc WHERE SEQ = 5 )+
	( select PCHAR FROM @pc WHERE SEQ = 6 )

Codo Ergo Sum

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-02-28 : 15:11:38

awesome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources