and more in a single search tool across platforms. Read the announcement here. |
02/22/2024 04:27 AM - edited 02/22/2024 06:23 AM
hello i have this use case :
Scenario:
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?
Solved! Go to Solution.
02/22/2024 09:25 PM
@ray refer this article :
Solved: Username Generation Rule - Saviynt Forums - 11630
02/22/2024 09:32 PM
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%'