Generate UserName from Existing table

  • 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.

  • 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/

  • Michael L John - Wednesday, March 7, 2018 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?

    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.

  • yogi123 - Wednesday, March 7, 2018 1:22 PM

    Michael L John - Wednesday, March 7, 2018 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?

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Wednesday, March 7, 2018 1:58 PM

    yogi123 - Wednesday, March 7, 2018 1:22 PM

    Michael L John - Wednesday, March 7, 2018 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?

    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.


  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • yogi123 - Wednesday, March 7, 2018 2:04 PM

    Jeff Moden - Wednesday, March 7, 2018 1:58 PM

    yogi123 - Wednesday, March 7, 2018 1:22 PM

    Michael L John - Wednesday, March 7, 2018 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?

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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_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

    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>

  • 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.

  • yogi123 - Wednesday, March 7, 2018 2:36 PM

    ScottPletcher - Wednesday, March 7, 2018 2:08 PM

    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>

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • yogi123 - Wednesday, March 7, 2018 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_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

    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>

    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/

  • ScottPletcher - Wednesday, March 7, 2018 2:47 PM

    yogi123 - Wednesday, March 7, 2018 2:36 PM

    ScottPletcher - Wednesday, March 7, 2018 2:08 PM

    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>

    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

  • Jason A. Long - Wednesday, March 7, 2018 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.

    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/

  • Jason A. Long - Wednesday, March 7, 2018 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.

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • Michael L John - Wednesday, March 7, 2018 2:48 PM

    yogi123 - Wednesday, March 7, 2018 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_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

    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>

    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