Christian Izaguirre Christian Izaguirre - 1 year ago 94
SQL Question

Modifying Duplicate UserName by Using the Count() Function, SQL 2012

I'm given a list Users and I need to create usernames for said Users but they must follow a certain business rule. Username = first 3 letters of first name + first 3 of last name and if the username exists add a numeric value like 1 after.

Ex. If there are 3 Chirs Henz then the user names should be ChiHen, ChiHen1, ChiHen2.

IF(NOT EXISTS(SELECT UserName FROM SystemUser WHERE SystemUser.UserName = UserName))
SET UserName = UserName + CAST((SELECT COUNT(1) FROM SystemUser WHERE UserName LIKE UserName+'%') AS NVARCHAR),

@UserUpload is a user defined table to hold the data. And I'm using Count() to count how many ChiHen I have to add the correct number at the end. This is my results when I run this.

Answer Source

Another option is to use an ad-hoc tally table

Declare @Users table (ID int,UserName varchar(25),FullName varchar(50))
Insert into @Users values
(1,'TESUSE','Test User'),
(2,'TESUSE1','Test User'),
(3,'TESUSE3','Test User')    --<<< Notice that Test User 2 is missing

Declare @NewFirst varchar(50)='Test'
Declare @NewLast varchar(50)='User'

Select Top 1 *
 From (
         Select RowNr,NewUserName=Upper(Concat(Left(@NewFirst,3),Left(@NewLast,3), IIF(RowNr-1=0,NULL,RowNr-1)))
         From (
                Select RowNr=Row_Number() over (Order By (Select NULL))
                 From (Select N From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)) N1, 
                      (Select N From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)) N2
              ) N
      ) A
  Where NewUserName Not in (Select UserName From @Users)
  Order By RowNr 


RowNr   NewUserName
3       TESUSE2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download