Jun 28, 2006

Generate Unique User IDs

This requirement was actually a question in the discussion forum. I thought I will keep a copy of my solution here for reference.

Okay, so here it goes.

I am taking the narration from the actual post (thanks to him), with a few changes.

Lets say I have a table like this.

create table User_Master
(
id int identity(1,1) primary key,
FirstName varchar(50),
LastName varchar(50),
username varchar(100)
)


I want to create some unique entries for username – I don’t want to touch the username that already has a value. But I need to update the username for which the current value is ‘’.

Rules for creation of the username value for update:

Try creating the username as the first 2 letters of FirstName and first 2 letters of LastName.
If the username exists in the database then the create with first 3 letters of FirstName and first 3 letters of LastName
If the username exists in the database then the create with first 4 letters of FirstName and first 4 letters of LastName.
This should go on till I get a unique value.

Examples:
Michael Bach -----> miba
Franz Mueller -----> frmu

Now say I have a name
Franziska Mueller -----> framue ( Since frmu already exist)

Let me put in some data into the table

insert into User_Master (FirstName,LastName,username) values('Michael','Bach','')
insert into User_Master (FirstName,LastName,username) values('Franz','Mueller','')
insert into User_Master (FirstName,LastName,username) values('Franziska','Mueller','')


Now we have set up the scenario. We need to generate the username.
For this I will be using Temp table to hold numbers from 1 to 50.
This is for selecting upto 50 characters in the FirstName and LastName and search with the existing usernames. You can tune it to meet your purpose.

This is the temp table I use. I am using the seed as 2 since I need to start from 2 letters from first and the last name for my username.

select top 50 identity(int,2,1) as id into #temp from sysobjects

Now I cannot do a batch update to my table, since The old value should be updated when i search for existence.

This is how I do it. I will loop through and search for the rows which has username =''.
Take the first row that has username = ''. And then update the username. I will keep doing this till there are no more rows that has username = ''

declare @a int
declare @rowcount int
select @a = min(id) from User_Master where username = ''
set @rowcount = @@rowcount


while(@rowcount > 0)
begin
update a
set username = (select top 1 left(a.firstname,b.id) + left(a.lastname,b.id)

from #temp b
where not exists

(
select 1 from User_Master c
where c.username =left(a.firstname,b.id) + left(a.lastname,b.id)
)
order by b.id
)
from

User_Master a
where

a.id = @a

set @rowcount = @@rowcount
select @a = min(id) from User_Master where username = '' and id> @a
end



The logic above is quite straightforward except for this snippet used set the username

select top 1 left(a.firstname,b.id) + left(a.lastname,b.id)
from #temp b where not exists
(
select 1 from User_Master c
where c.username =left(a.firstname,b.id) + left(a.lastname,b.id)
)

order by b.id

What I try to do here is take the firstname and lastname of the current row to be updated and join it with the temp table and get all possible usernames (2 char from first and 2 from last name, 3 chars, 4 chars, ..... upto 50 chars and get the first username that does not exist in the User_Master and use it to update the user name.


Now try this.

select * from User_Master

Here is the result

id FirstName LastName username
---- ---------- --------- ---------
1 Michael Bach MiBa
2 Franz Mueller FrMu
3 Franziska Mueller FraMue


-----------------------------------------------------------------------------------------------------

I should thank Erland for his article on arrays in SQL Server. If you have questions on why I used the #temp table and how that join worked, you will find the answers here.

http://www.sommarskog.se/arrays-in-sql.html

Rest assured, its worth every minute you spend on this.

3 comments:

Anonymous said...

Hi Omnibuzz,
This is great but my request is ont the same line but a little different i am trying to modify but not successfull.


Here is my requirement.I have been trying various things but i am not successfull till this point

Here is an example
LastName Fname Uname
Smith Jo Smith
Smith Jo SmithJ
Smith Jo Smithjo
smith Jo smitj
smith Jo smitjo
smith Jo smij
smith Jo smijo

Basically Username should not be greater than 7 and and can be just the lastname if that username doesn't exist and also ,if greater than 7 just take first 7 chars

if lastname already exists we have take one char from firstname and then goes on as outlined in the example


Any suggestions would be greatly appreciated.


Thanks,

Omnibuzz said...

Check this out...
data:

create table User_Master
(
id int identity(1,1) primary key,
FirstName varchar(50),
LastName varchar(50),
username varchar(100)
)

insert into User_Master (FirstName,LastName,username) values('Michael','Bach','')
insert into User_Master (FirstName,LastName,username) values('Franz','Mueller','')
insert into User_Master (FirstName,LastName,username) values('Franziska','Mueller','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')
insert into User_Master (FirstName,LastName,username) values('Jo','Smith','')


And the query

-- Starts here

select top 50 identity(int,0,1) as id into #temp from sysobjects

declare @a int
declare @rowcount int
select @a = min(id) from User_Master where username = ''
set @rowcount = @@rowcount

while(@rowcount > 0)
begin
update a
set username = (select top 1 left(left(a.lastname,len(lastname) - b.id) + left(a.FirstName,d.id),7)
from #temp b, #temp d
where not exists
(
select 1 from User_Master c
where c.username =left(left(a.lastname,len(lastname) - b.id) + left(a.FirstName,d.id),7)
)
and len(lastname) > b.id
and not(b.id >=1 and d.id = 0)
order by b.id,d.id
)
from
User_Master a
where
a.id = @a

set @rowcount = @@rowcount
select @a = min(id) from User_Master where username = '' and id> @a
end

drop table #temp

-- Ends here

-Omni

Unknown said...

Hi Omnibuzz,
Thanks for the information above.

I too am looking to generate usernames using SQL, though the format I am looking for, is slightly different.


Here is my requirement - If a username exists, the system should append a number at the end of the username, such that the username is unique.

For example -
LN FN Username Display Name
Smith John JSmith John Smith
Smith John JSmith1 John Smith 1
Smith Jack JSmith2 Jack Smith

Basically Username should not be greater than 10 characters.
Any suggestions would be greatly appreciated.

- Madan

Post a Comment