March 7, 2018 at 12:58 pm
Hi Guys,
I have one new challenge to generate UserName from the table, below i provide some sample data
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50))
INSERT INTO #TEMP VALUES ('1','JAMES','DOYEL')
INSERT INTO #TEMP VALUES ('2','SCOTT','THOMAS')
INSERT INTO #TEMP VALUES ('3','JAMES','DOYEL')
INSERT INTO #TEMP VALUES ('4','SHAUN','JERMANEY')
INSERT INTO #TEMP VALUES ('5','SCOTT','THOMAS')
INSERT INTO #TEMP VALUES ('6','JAMES','DOYEL')
INSERT INTO #TEMP VALUES ('7','JAMES','DOYEL')
I need to generate UserName from their First and Last name, the expected output as below
ID FIRSTNAME LASTNAME username
1 JAMES DOYEL JAMES.DOYEL
2 SCOTT THOMAS SCOTT.THOMAS
3 JAMES DOYEL JAMES.DOYEL1
4 SHAUN JERMANEY SHAUN.JERMANEY
5 SCOTT THOMAS SCOTT.THOMAS1
6 JAMES DOYEL JAMES.DOYEL2
7 JAMES DOYEL JAMES.DOYEL3
is anyone design this in a past, please help me on this.
Thanks in advance.
March 7, 2018 at 1:15 pm
What have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.
How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2018 at 1:22 pm
Michael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?
Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
March 7, 2018 at 1:58 pm
yogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2018 at 2:04 pm
Jeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
March 7, 2018 at 2:08 pm
SELECT ID, FIRSTNAME, LASTNAME,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_counts
And, to update the original table:
ALTER TABLE #TEMP ADD username varchar(100) NULL;
UPDATE T
SET username = U.username
FROM #TEMP T
INNER JOIN (
SELECT ID,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_counts
) AS U ON U.ID = T.ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2018 at 2:14 pm
yogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2018 at 2:36 pm
ScottPletcher - Wednesday, March 7, 2018 2:08 PM
SELECT ID, FIRSTNAME, LASTNAME,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_countsAnd, to update the original table:
ALTER TABLE #TEMP ADD username varchar(100) NULL;
UPDATE T
SET username = U.username
FROM #TEMP T
INNER JOIN (
SELECT ID,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_counts
) AS U ON U.ID = T.ID
Thanks it works fine...
I have another situation
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')
I have UserName there for some IDs , I only need to update where blanks are, doesn't matter which ID comes first but it has to be in JACK.Rinse7, JACK.Rinse8 and so on.
Can you please do that>
March 7, 2018 at 2:41 pm
The really easy thing would be simply join the modern era and just use email address like every other website built in the last 10 years.
Nothing will make me stop doing business with a company faster than a website that won't let me login because I can't remember some nonsensical username.
March 7, 2018 at 2:47 pm
yogi123 - Wednesday, March 7, 2018 2:36 PMScottPletcher - Wednesday, March 7, 2018 2:08 PMThanks it works fine...
I have another situation
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')I have UserName there for some IDs , I only need to update where blanks are, doesn't matter which ID comes first but it has to be in JACK.Rinse7, JACK.Rinse8 and so on.
Can you please do that>
I can, but that's more complex, don't have time right now. But I can give you some hints to write the code if you want:
To see if a name pattern is already assigned, you need to check for:
username LIKE FIRSTNAME + '.' + LASTNAME + '[0-9]%'
When you get the last value, you need to pull the chars starting with the first numeric one and convert them to an int value so you can get the true max value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2018 at 2:48 pm
yogi123 - Wednesday, March 7, 2018 2:36 PMScottPletcher - Wednesday, March 7, 2018 2:08 PM
SELECT ID, FIRSTNAME, LASTNAME,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_countsAnd, to update the original table:
ALTER TABLE #TEMP ADD username varchar(100) NULL;
UPDATE T
SET username = U.username
FROM #TEMP T
INNER JOIN (
SELECT ID,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_counts
) AS U ON U.ID = T.IDThanks it works fine...
I have another situation
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')I have UserName there for some IDs , I only need to update where blanks are, doesn't matter which ID comes first but it has to be in JACK.Rinse7, JACK.Rinse8 and so on.
Can you please do that>
Have you tried this yourself? What have you attempted?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2018 at 2:49 pm
ScottPletcher - Wednesday, March 7, 2018 2:47 PMyogi123 - Wednesday, March 7, 2018 2:36 PMScottPletcher - Wednesday, March 7, 2018 2:08 PMThanks it works fine...
I have another situation
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')I have UserName there for some IDs , I only need to update where blanks are, doesn't matter which ID comes first but it has to be in JACK.Rinse7, JACK.Rinse8 and so on.
Can you please do that>
I can, but that's more complex, don't have time right now. But I can give you some hints to write the code if you want:
To see if a name pattern is already assigned, you need to check for:
username LIKE FIRSTNAME + '.' + LASTNAME + '[0-9]%'
When you get the last value, you need to pull the chars starting with the first numeric one and convert them to an int value so you can get the true max value.
Ok Thanks. Will try and let see.
Thanks for your help
March 7, 2018 at 2:51 pm
Jason A. Long - Wednesday, March 7, 2018 2:41 PMThe really easy thing would be simply join the modern era and just use email address like every other website built in the last 10 years.Nothing will make me stop doing business with a company faster than a website that won't let me login because I can't remember some nonsensical username.
Yes this!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2018 at 2:52 pm
Jason A. Long - Wednesday, March 7, 2018 2:41 PMThe really easy thing would be simply join the modern era and just use email address like every other website built in the last 10 years.Nothing will make me stop doing business with a company faster than a website that won't let me login because I can't remember some nonsensical username.
Bit extreme, don't you think? I guess you use the same email address for everything; I certainly don't. I use bogus ones for a lot of (retail) companies so they don't have my real email address.
Also, I would run like he!! from any bank or credit card or brokerage company, as examples, that used email to log in. How many gazillions of people know your email?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2018 at 2:58 pm
Michael L John - Wednesday, March 7, 2018 2:48 PMyogi123 - Wednesday, March 7, 2018 2:36 PMScottPletcher - Wednesday, March 7, 2018 2:08 PM
SELECT ID, FIRSTNAME, LASTNAME,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_countsAnd, to update the original table:
ALTER TABLE #TEMP ADD username varchar(100) NULL;
UPDATE T
SET username = U.username
FROM #TEMP T
INNER JOIN (
SELECT ID,
FIRSTNAME + '.' + LASTNAME +
CASE WHEN row_num = 1 THEN '' ELSE CAST(row_num - 1 AS varchar(10)) END AS username
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY FIRSTNAME, LASTNAME ORDER BY ID) AS row_num
FROM #TEMP
) AS name_counts
) AS U ON U.ID = T.IDThanks it works fine...
I have another situation
CREATE TABLE #TEMP
(ID VARCHAR(10),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
USERNAME VARCHAR(50))INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326024','JACK','Rinse','JACK.Rinse3')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1326048','JACK','Rinse','JACK.Rinse')
INSERT INTO #TEMP VALUES ('1328843','JACK','Rinse','JACK.Rinse1')
INSERT INTO #TEMP VALUES ('1335935','JACK','Rinse','JACK.Rinse2')
INSERT INTO #TEMP VALUES ('1350166','JACK','Rinse','')
INSERT INTO #TEMP VALUES ('1351545','JACK','Rinse','JACK.Rinse4')
INSERT INTO #TEMP VALUES ('1351548','JACK','Rinse','JACK.Rinse5')
INSERT INTO #TEMP VALUES ('1353102','JACK','Rinse','JACK.Rinse6')
INSERT INTO #TEMP VALUES ('1356524','JACK','Rinse','')I have UserName there for some IDs , I only need to update where blanks are, doesn't matter which ID comes first but it has to be in JACK.Rinse7, JACK.Rinse8 and so on.
Can you please do that>
Have you tried this yourself? What have you attempted?
So far I found the max values from the string
select firstname,
lastname,
firstname + '.'+lastname + cast(max(test)+1 as varchar(10))as number
from
(
SELECT id,
firstname,
LASTNAME,
USERNAME,case when LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) = '' then 0
else LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) end as [test]
FROM (
SELECT id,
firstname,
LASTNAME,
USERNAME,subsrt = SUBSTRING(USERNAME, pos, LEN(USERNAME))
FROM (
SELECT id,
firstname,
LASTNAME,
USERNAME, pos = PATINDEX('%[0-9]%', USERNAME)
FROM #TEMP
where username <> ''
) d
) t
)a
group by a.FIRSTNAME, a.LASTNAME
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply