See if username exists and suggest a username

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)

See if username exists and suggest a username

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-08 : 10:33:13

Hello,
I have a script that searches to see if a username exists via a SP and returns a return value.

However, i would like to be able to suggest other usernames e.g. if someone chooses afrika and afrika exists, it would return:

That username afrika exists try others e.g.
i-afrika, e-afrika, 1afrika (Which do not exist)

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-08 : 10:39:17

Extract a character from newid() and add that with the name
Something like


if exists(select name from tbl where name='afrika')
Declare @s varchar(50)
select @s=left(newid(),1)+'-'+name from tbl
print @s

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-08 : 10:57:59

hi madhivanan,
thanks for the code, however
the first part checks to see if the record exists, but
the second part of your code does not check to see if the record exists ?

It just selects the first part of the newid function.

would it be efficient to run again

if exists(select name from tbl where name='afrika')
Declare @s varchar(50)
if exists (select @s=left(newid(),1)+'-'+name from tbl where username = @s)
--...and run multiple times, to suggest different names
print @s

Now if i have over 1million registered users, would this be efficient ?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-11 : 09:25:12

Any Other Advice on this?

thanks
Afrika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-11 : 09:43:43

quote:
Now if i have over 1million registered users, would this be efficient ?

Do you really want to do this?
The simple way is let the user type the name and check for the existance through query

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-11 : 10:00:13

Do i really want to do that ?

My question, is how do systems, do intelligent searches, where they can suggest usernames for you.

What i am exploring is a search whereby if i user, chooses madhivanan and it exists, the system, can suggest other alternatives which do not exist.

Basically an efficient system, that woudl be able to scale (Reason for that outrageous sum) However MS Sql, should be able to cater for that, but is it efficient and how efficiently can it be done.

I have come up with this

if exists(select name from tbl where name='Madhivanan')
Declare @s varchar(50)
--Now user Madhivanan exists, so do another search
--(Append i-) i-Madhivanan and see if it exists, and if not suggest to user
if exists (select @s=left(newid(),1)+'-'+Madhivanan
 from tbl 
--...and run multiple times, to suggest different names
print @s

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-11 : 10:43:35

most things i have seen like this (AOL, some online games) use a canned response. You type in a name, if it is taken, it suggests new ones for you. It takes what you input (Freddy), and adds standard strings to it (Freddy123,Freddy_123,FreddyOnline). If those are taken, it just seems too bad. I think once you tell your user that a name is taken, they will get the idea of how to make it slightly different.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Mountain_Nerd
Starting Member

28 Posts

Posted - 2005-07-11 : 11:10:10

Why not append integers to the end of the suggested name, so that the searched name is "afrika" and the suggested names are "afrika1, afrika2, etc."? This should make it easier to find the next available user name using MAX.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources