Advance Config for Systemusername Creation Excluding 0's and 1's

[jbaskaran]

Hi, Can anyone please help/assist in creating an Advance Config logic where it excludes 0's & 1's while creating a unique systemusername by satisfying the below logic:

"Logic - 'lastname'+'first character of the firstname'+'numeric value (2-9)' (max length is 8  )

Currently we have a basic rule but that includes 0's & 1's like 

We can try using Custom string like 2,3,4,5,6,7,8,9,22,23 ..... 32,33.. but that will end up having multiple rules.

So can anyone please suggest on how to achieve this using Advance query. Thanks in advance

[shivmano]

@jbaskaran , You can try with the below advanced query and see if it helps 

CONCAT(SUBSTRING(lastname,1,6),SUBSTRING(firstname,1,1),(FLOOR(RAND() * (9-2 + 1)) + 2))

SUBSTRING(lastname,1,6) - Gets the first 6 characters from the lastname (summing up the max length to 8 )
SUBSTRING(firstname,1,1) - Gets the first character of the firstname
SUBSTRING((FLOOR(RAND() * (9-2 + 1)) + 2),1,1) - Gets the numeric value between 2 and 9 as integer including 2 and 9

[jbaskaran]

HI Shivmano, Thank you for your update but we should also include the logic like what if last name character is 6 then it should take 2 characters from first name.

First it should take characters and then try to add numeric value excluding 0's & 1's.

So its like Rule 1:

Users - > LN -> InitialChar -> 7

Users - > FN -> InitialChar -> 1

Rule 2:

Users - > LN -> InitialChar -> 6

Users - > FN -> InitialChar -> 2

Rule 3:

Users - > LN -> InitialChar -> 5

Users - > FN -> InitialChar -> 3

Rule 4:

Users - > LN -> InitialChar -> 4

Users - > FN -> InitialChar -> 4

Rule 5:

Users - > LN -> InitialChar -> 6

Users - > FN -> InitialChar -> 1

Auto Increment -> 2 (Here in this part since we have provided Auto Increment as 2, which will contain 0's & 1's after few combination, so how to control this by not ending up having multiple rules in place.

[shivmano]

When you say the max length to be 8, does that include the lastname and firstname chars with the numeric values? if the above shared are the possible combinations, then you can add  # or ### as separator in the advanced config to split the rules (similar to the one shared below) or you can also use a case statement if you need a single query 

CONCAT(LEFT(lastname,6),LEFT(firstname,1),(FLOOR(RAND() * (9-2 + 1)) + 2))#
CONCAT(LEFT(lastname,4),LEFT(firstname,4),(FLOOR(RAND() * (9-2 + 1)) + 2))#
CONCAT(LEFT(lastname,5),LEFT(firstname,3),(FLOOR(RAND() * (9-2 + 1)) + 2))#
CONCAT(LEFT(lastname,6),LEFT(firstname,2),(FLOOR(RAND() * (9-2 + 1)) + 2))#
CONCAT(LEFT(lastname,7),LEFT(firstname,1),(FLOOR(RAND() * (9-2 + 1)) + 2))

[jbaskaran]

Hi Shivmano, It should contain 8 characters without numeric value and if the LN & FN combination logic exceeds with the existing username then it should add numberic values.

[jbaskaran · Accepted solution]

Hi, We tried to have something like below to fix it, which will help creating 25 users with same LN & FN by adding numberic value excluding 0's & 1's. 

concat(left(lastname,7),left(firstname,1))###concat(left(lastname,6),left(firstname,2))###concat(left(lastname,5),left(firstname,3))###concat(left(lastname,4),left(firstname,4))###concat(left(lastname,6),left(firstname,1),'2')###concat(left(lastname,6),left(firstname,1),'3')###concat(left(lastname,6),left(firstname,1),'4')###concat(left(lastname,6),left(firstname,1),'5')###concat(left(lastname,6),left(firstname,1),'6')###concat(left(lastname,6),left(firstname,1),'7')###concat(left(lastname,6),left(firstname,1),'8')###concat(left(lastname,6),left(firstname,1),'9')###concat(left(lastname,6),left(firstname,1),'22')###concat(left(lastname,6),left(firstname,1),'23')###concat(left(lastname,6),left(firstname,1),'24')###concat(left(lastname,6),left(firstname,1),'25')###concat(left(lastname,6),left(firstname,1),'26')###concat(left(lastname,6),left(firstname,1),'27')###concat(left(lastname,6),left(firstname,1),'28')###concat(left(lastname,6),left(firstname,1),'29')###concat(left(lastname,6),left(firstname,1),'32')###concat(left(lastname,6),left(firstname,1),'33')###concat(left(lastname,6),left(firstname,1),'34')###concat(left(lastname,6),left(firstname,1),'35'###concat(left(lastname,6),left(firstname,1),'36')

Note : If required for more count of combination then we need to add 37,38,....