November 3, 2016 at 3:40 am
Hi All,
I am having user table as below
Create table #TEMP_USER
(
ID int identity (1,1) ,
USERNAME nvarchar(50)
);
I want to insert usernames in the above table from below table.
Create table #Employee
(
ID int identity (1,1) ,
FirstName varchar(40),
LastName varchar(50)
);
insert into #Employee values('Abhas','Patil')
insert into #Employee values('Aarav','Patil')
insert into #Employee values('Sanjay','sutar')
insert into #Employee values('Swati','jadhav')
insert into #Employee values('varsha','mane')
while creating username, ineed to consider first letter of firstname + lastname.Also need to check whether username is available in #TEMP_USER. If available then i need to increment by i as below for above data.. usernames would be as below.
APatil
APatil1
Ssutar
Sjadhav
vmane
Thanks,
Abhas.
November 3, 2016 at 4:19 am
Here is a suggestion, should be enough to get you passed this hurdle
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;
Create table #Employee
(
ID int identity (1,1) ,
FirstName varchar(40),
LastName varchar(50)
);
insert into #Employee (FirstName,LastName)
values('Abhas','Patil')
,('Aarav','Patil')
,('Sanjay','sutar')
,('Swati','jadhav')
,('varsha','mane');
SELECT
E.ID
,E.FirstName
,E.LastName
,SUBSTRING(E.FirstName,1,1) + E.LastName
+ ISNULL(NULLIF(
CONVERT(VARCHAR(12),ROW_NUMBER() OVER
(
PARTITION BY SUBSTRING(E.FirstName,1,1) + E.LastName
ORDER BY E.ID
) - 1
,0),'0'),'') AS UserName
FROM #Employee E;
ID FirstName LastName UserName
--- ---------- --------- ---------
1 Abhas Patil APatil
2 Aarav Patil APatil1
4 Swati jadhav Sjadhav
3 Sanjay sutar Ssutar
5 varsha mane vmane
November 3, 2016 at 6:31 am
Thanks Eirikur,
your code works fine, but while inserting into TEMP_USER table, after creating username, getting issue. For first time insert it works fine but if next day again 'aarav patil' and 'amit patil' comes then it is inserting as apatil and apatil1.
Thanks for your help.
Regards
Abhas.
November 3, 2016 at 10:32 am
abhas (11/3/2016)
Thanks Eirikur,your code works fine, but while inserting into TEMP_USER table, after creating username, getting issue. For first time insert it works fine but if next day again 'aarav patil' and 'amit patil' comes then it is inserting as apatil and apatil1.
Thanks for your help.
Regards
Abhas.
The reoccurring inserts part was missing in your initial post, it is important that questions are as accurate and detailed as possible.
😎
Here is the solution adjusted to the latest requirements.
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#TEMP_USER') IS NOT NULL DROP TABLE #TEMP_USER;
Create table #TEMP_USER
(
ID int identity (1,1) ,
USERNAME nvarchar(50)
);
IF OBJECT_ID(N'tempdb..#Employee') IS NOT NULL DROP TABLE #Employee;
Create table #Employee
(
ID int identity (1,1) ,
FirstName varchar(40),
LastName varchar(50)
);
insert into #Employee (FirstName,LastName)
values('Abhas','Patil')
,('Aarav','Patil')
,('Sanjay','sutar')
,('Swati','jadhav')
,('varsha','mane');
;WITH BASE_DATA(USERNAME) AS
(
SELECT
SUBSTRING(TU.USERNAME,1,ISNULL(NULLIF(PATINDEX('%[0-9]%',TU.USERNAME)-1,-1),LEN(TU.USERNAME)))
FROM #TEMP_USER TU
UNION ALL
SELECT
SUBSTRING(E.FirstName,1,1) + E.LastName
FROM #Employee E
)
INSERT INTO #TEMP_USER(USERNAME)
SELECT
BD.USERNAME + ISNULL(NULLIF(
CONVERT(VARCHAR(12),ROW_NUMBER() OVER
(
PARTITION BY BD.USERNAME
ORDER BY BD.USERNAME
) - 1
,0),'0'),'') AS UserName
FROM BASE_DATA BD
EXCEPT
SELECT
TU.USERNAME
FROM #TEMP_USER TU;
TRUNCATE TABLE #Employee;
insert into #Employee (FirstName,LastName)
values('Amar','Patil')
,('Aarav','Patil')
,('Sanjay','sutar')
,('Swati','jadhav')
,('varsha','mane');
;WITH BASE_DATA(USERNAME) AS
(
SELECT
SUBSTRING(TU.USERNAME,1,ISNULL(NULLIF(PATINDEX('%[0-9]%',TU.USERNAME)-1,-1),LEN(TU.USERNAME)))
FROM #TEMP_USER TU
UNION ALL
SELECT
SUBSTRING(E.FirstName,1,1) + E.LastName
FROM #Employee E
)
INSERT INTO #TEMP_USER(USERNAME)
SELECT
BD.USERNAME + ISNULL(NULLIF(
CONVERT(VARCHAR(12),ROW_NUMBER() OVER
(
PARTITION BY BD.USERNAME
ORDER BY BD.USERNAME
) - 1
,0),'0'),'') AS UserName
FROM BASE_DATA BD
EXCEPT
SELECT
TU.USERNAME
FROM #TEMP_USER TU;
SELECT
TU.USERNAME
FROM #TEMP_USER TU
Output
USERNAME
---------
APatil
APatil1
Sjadhav
Ssutar
vmane
APatil2
APatil3
Sjadhav1
Ssutar1
vmane1
November 3, 2016 at 11:35 pm
Thanks Eirikur,
your code works exactly as per requirement.
Sorry for mentioning incomplete question.
Thank you so much.
Regards,
Abhas.
November 4, 2016 at 12:55 am
You are very welcome.
😎
November 4, 2016 at 7:23 am
If the number itself is not important, the an easier way to do this might be to add an identity column to the table and create a persisted computed column that uses it as part of the name.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2016 at 1:15 am
Thanks Eirikur and Jeff.
Now again requirement has changed. Now instead of increment, new logic is as below:
First letter of firstname + LastName.
If username alreadu exists then create as:
First two letters of firstname + LastName. Could you please help on this?
Sample usernames as below
ID FirstName LastName UserName
--- ---------- --------- ---------
1 Abhas Patil APatil
2 Aarav Patil AaPatil
4 Swati jadhav Sjadhav
3 Sanjay sutar Ssutar
5 varsha mane vmane
6 Abhas Patil abpatil
7 Aarav Patil Aarpatil
Thanks,
Abhas.
November 7, 2016 at 1:35 am
abhas (11/7/2016)
Thanks Eirikur and Jeff.Now again requirement has changed. Now instead of increment, new logic is as below:
First letter of firstname + LastName.
If username alreadu exists then create as:
First two letters of firstname + LastName. Could you please help on this?
Sample usernames as below
ID FirstName LastName UserName
--- ---------- --------- ---------
1 Abhas Patil APatil
2 Aarav Patil AaPatil
4 Swati jadhav Sjadhav
3 Sanjay sutar Ssutar
5 varsha mane vmane
6 Abhas Patil abpatil
7 Aarav Patil Aarpatil
Thanks,
Abhas.
What if the first two letters are the same?
😎
Why not simply use the users email address?
November 7, 2016 at 2:00 am
hi,
if first two letters are same then use first three letters for second user and so on......
Thanks
Abhas.
November 7, 2016 at 2:04 am
abhas (11/7/2016)
hi,if first two letters are same then use first three letters for second user and so on......
Thanks
Abhas.
And if the first names are the same?
😎
November 7, 2016 at 2:59 am
Hi Eirikur,
Requirement is as below:
Need to create username. Logic is:
First letter of firstname + Lastname.
Eg. Firstname = Abhas and LastName = Patil then username = apatil
Now again case come like:
Firstname = Abhi and LastName = Patil then username = abpatil
case 3:
Firstname = Arun and LastName = Patil then username = arpatil
case 4
Firstname = Arjun and LastName = Patil then username = arjpatil
that means if username exists then instead of first letter of first name we need to increase it as first 2 letters of firstname and so on.
Thanks,
Abhas.
November 7, 2016 at 7:16 am
Notwithstanding Eirikur's statement re same first names
WITH x (ID,FirstName,LastName,UserName,RowNum) AS (
SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,
ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC)
FROM #Employee e
JOIN master.dbo.Tally t ON t.N BETWEEN 1 AND LEN(e.FirstName)
WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)
)
SELECT ID,FirstName,LastName,UserName
FROM x
WHERE RowNum = 1;
(Uses a tally table)
Far away is close at hand in the images of elsewhere.
Anon.
November 7, 2016 at 7:55 am
David Burrows (11/7/2016)
Notwithstanding Eirikur's statement re same first names
WITH x (ID,FirstName,LastName,UserName,RowNum) AS (
SELECT e.ID,e.FirstName,e.LastName,LEFT(e.FirstName,t.N)+e.LastName,
ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY LEN(LEFT(e.FirstName,t.N)+e.LastName) ASC)
FROM #Employee e
JOIN master.dbo.Tally t ON t.N BETWEEN 1 AND LEN(e.FirstName)
WHERE NOT EXISTS (SELECT * FROM #TEMP_USER u WHERE u.USERNAME = LEFT(e.FirstName,t.N)+e.LastName)
)
SELECT ID,FirstName,LastName,UserName
FROM x
WHERE RowNum = 1;
(Uses a tally table)
This fails as it returns duplicates in the generated user names
😎
Output
ID FirstName LastName UserName
--- ---------- --------- ---------
2 Aarav Patil APatil
4 Swati jadhav Sjadhav
5 varsha mane vmane
1 Abhas Patil APatil
3 Sanjay sutar Ssutar
November 7, 2016 at 8:45 am
Eirikur Eiriksson (11/7/2016)
This fails as it returns duplicates in the generated user names
Oh well it was close 😛
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply