Create a unique username from first and last name?

Question

I have a bunch of users in my database, and I want to reset all their usernames to be the first letters of their name, as well as their full name. As you can imagine, there are some deceptions. In this case, I would like to add “2” or “3” or something to the end of the username. How to write a query to create a unique username like this?

UPDATE user
SET username=lower(concat(substring(first_name,1,1), last_name), UNIQUETHINGHERE)

Answer 1

CREATE TABLE bar LIKE foo;

INSERT INTO bar (id,user,first,last)
(SELECT f.id,CONCAT(SUBSTRING(f.first,1,1),f.last,
     (SELECT COUNT(*) FROM foo f2
         WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,1)
               AND f2.last = f.last AND f2.id <= f.id
         )),f.first,f.last from foo f);

DROP TABLE foo;
RENAME TABLE bar TO foo;

It depends on the primary key id, so for each record inserted in bar, we only consider duplicates found in foo, with idless than bar.id.

Given foo:

select * from foo;
+----+------+--------+--------+
| id | user | first  | last   |
+----+------+--------+--------+
|  1 | aaa  | Roger  | Hill   | 
|  2 | bbb  | Sally  | Road   | 
|  3 | ccc  | Fred   | Mount  | 
|  4 | ddd  | Darren | Meadow | 
|  5 | eee  | Sharon | Road   | 
+----+------+--------+--------+

INSERT bar, :

select * from bar;
+----+----------+--------+--------+
| id | user     | first  | last   |
+----+----------+--------+--------+
|  1 | RHill1   | Roger  | Hill   | 
|  2 | SRoad1   | Sally  | Road   | 
|  3 | FMount1  | Fred   | Mount  | 
|  4 | DMeadow1 | Darren | Meadow | 
|  5 | SRoad2   | Sharon | Road   | 
+----+----------+--------+--------+

---

"1" ,

INSERT INTO bar (id,user,first,last)
(SELECT f3.id,
        CONCAT(
            SUBSTRING(f3.first,1,1),
            f3.last,
            CASE f3.cnt WHEN 1 THEN '' ELSE f3.cnt END),
        f3.first,
        f3.last
 FROM (
    SELECT
        f.id,
        f.first,
        f.last,
        (
            SELECT COUNT(*) 
            FROM foo f2
            WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,1)
            AND f2.last = f.last AND f2.id <= f.id
        ) as cnt
    FROM foo f) f3)

Answer 2

:

SELECT max(username)
FROM user
WHERE username LIKE concat(lower(concat(substring(first_name,1,1),lastname), '%')

"" . , .

, , . / , , . / , , .

Answer 3

Inspired by unutbu answer: there is no need to create an additional table and not several queries:

UPDATE USER a
    LEFT JOIN (
        SELECT USR_ID, 
            REPLACE(
                CONCAT(
                    SUBSTRING(f.`USR_FIRSTNAME`,1,1),
                    f.`USR_LASTNAME`,
                    (
                        (SELECT IF(COUNT(*) > 1, COUNT(*), '')
                            FROM USER f2
                            WHERE SUBSTRING(f2.`USR_FIRSTNAME`,1,1) = 
                                SUBSTRING(f.`USR_FIRSTNAME`,1,1)
                                AND f2.`USR_LASTNAME` = f.`USR_LASTNAME`
                                AND f2.`USR_ID` <= f.`USR_ID`)
                        )
                ),
                ' ',
                '') as login
        FROM USER f) b
    ON a.USR_ID = b.USR_ID
    SET a.USR_NICKNAME = b.login

Answer 4

Nevermind .... I just found cheats:

select LOWER(CONCAT(SUBSTRING(first_name,1,1),last_name)) as new_login,count(* ) as cnt from wx_user group by new_login having count(* )>1;

And install them manually. There was only a handful.