Creat an Auto Increment unique Username Generation Rule

[ray]

hello i have this use case :

Scenario:

• Username creation (unique) needs to be like "EX000001, EX000002, EX000003 ~ ”
• Username should have a max length of 8. Characters 'EX' followed by 6-digit numbers autoincremented.
• After the 9th username, it doesn't add the increment as per the requirements -  the number of digits starts increasing. The digits length should remain at 6.'

i tried this below

SELECT
CONCAT(
"XE",
CASE
WHEN (LENGTH((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1)) = 1) THEN CONCAT('00000', CONVERT((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1), CHAR))
WHEN (LENGTH((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1)) = 2) THEN CONCAT('0000', CONVERT((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1), CHAR))
WHEN (LENGTH((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1)) = 3) THEN CONCAT('000', CONVERT((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1), CHAR))
WHEN (LENGTH((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1)) = 4) THEN CONCAT('00', CONVERT((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1), CHAR))
WHEN (LENGTH((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1)) = 5) THEN CONCAT('0', CONVERT((MAX(SUBSTRING(USERNAME, 3, LENGTH(USERNAME))) + 1), CHAR))
END
) AS ID
FROM
users
WHERE
USERNAME LIKE 'XE%'
AND LENGTH(USERNAME) = 8;

do anyone have a feedback if it will work properly?

[Manu269]

@ray refer this article :

Solved: Username Generation Rule - Saviynt Forums - 11630

[rushikeshvartak · Accepted solution]

SELECT CONCAT('EX', LPAD(IFNULL(MAX(CAST(SUBSTRING(USERNAME, 3) AS UNSIGNED)), 0) + ROW_NUMBER() OVER (), 6, '0')) AS ID
FROM users
WHERE USERNAME LIKE 'EX%'