Advance Query for Username generation with Only Uppercase letters

[SanjeetaRao]

Hi,

We need to have username in the format 2 random Alphabets(Uppercase) and 5 Random Numbers.

Example:TK54768, YR28623

But Saviynt OOB generation rule doesn't support only Uppercase Alphabets. 

Can someone please help us with a sample advance query to generate username in the required logic.

2 random Alphabets(Uppercase) and 5 Random Numbers

[rushikeshvartak]

SELECT
CONCAT(
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10),
FLOOR(RAND() * 10)
) AS username;

[SanjeetaRao]

Hi @rushikeshvartak ,

The above logic created the below username:

[76, 73, 51, 55, 52, 51, 51]

[rushikeshvartak · Accepted solution]

SELECT
CONVERT(
CONCAT(
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48)
) USING utf8mb4
) AS username;

[SanjeetaRao · Accepted solution]

Hi Rishi,

We modified the query to :

SELECT
CONVERT(
CONCAT(
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 26) + 65),
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48)
) USING utf8mb4
) AS username;

and it worked as per our requirement. Thanks for your help!