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.