January 29, 2016 at 12:22 am
Hi All,
I want to create username for new registered students, but while creating username i want to do below.
1- Username should be First letter of FirstName + LastName.
2 - UserName should not > 20 characters
3- Username should not duplicate and it should increased by 1 if already exists.
I am creating username on daily basis. For example, If today three users are registred as below :
FirstName = Abhas And LastName = Jadhav
FirstName = Ashwin And LastName = Jadhav
FirstName = Akash And LastName = Jadhav
and they are already inserted in one table say studentDetails. Now I want to select these three records
Then i want to select these records and create usernames as below:
aJadhav
aJadhav1
aJadhav2
Could you please guide how can i do this?
Thanks,
Abhas.
January 29, 2016 at 5:05 am
create table UserName (FirstName varchar(50),LastName varchar(50))
insert into Username select 'Abhas' ,'Jadhav'
Union
select 'Ashwin' ,'Jadhav'
UNION
select 'Akash','Jadhav'
UNION
select 'Bbhas' ,'Jadhav'
Union
select 'Bshwin' ,'Jadhav'
UNION
select 'Bkash','Jadhav'
with cte as
(
select newid () sysid ,(SUBSTRING(FirstName,1,1)) firstname ,lastname from username
),
cte2 as
(
select sysid,firstname,lastname,convert (varchar(100),ROW_NUMBER() over(partition by firstname,lastname order by sysid)) as rownumber from cte
)
select (firstname + lastname + replace((rownumber -1),0,'')) as fullname from cte2
January 29, 2016 at 9:46 am
TEJABI (1/29/2016)
create table UserName (FirstName varchar(50),LastName varchar(50))insert into Username select 'Abhas' ,'Jadhav'
Union
select 'Ashwin' ,'Jadhav'
UNION
select 'Akash','Jadhav'
UNION
select 'Bbhas' ,'Jadhav'
Union
select 'Bshwin' ,'Jadhav'
UNION
select 'Bkash','Jadhav'
with cte as
(
select newid () sysid ,(SUBSTRING(FirstName,1,1)) firstname ,lastname from username
),
cte2 as
(
select sysid,firstname,lastname,convert (varchar(100),ROW_NUMBER() over(partition by firstname,lastname order by sysid)) as rownumber from cte
)
select (firstname + lastname + replace((rownumber -1),0,'')) as fullname from cte2
--TOMORROW we get another user
insert into #Username select 'Aaron' ,'Jadhav';
what happens and can you guarntee it?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply