SystemUsername generation using advanced sql query

[priyag]

Hello all 

need a help for  generating  the StemUserName according to the following conditions.All lower case, remove diacritics <firstname>.<lastname>[<xxx>] Limited to 20 characters, if necessary truncate last name ,Uniqueness, In case of duplicate, append 3 digits starting with 001 and incrementing as needed. 

[NM]

Hi @priyag , 

Case when (select count(*) from users u where u.syatemusername like concat(lower(u.firstname),'.',substring(lower(u.lastname),1,20),'001')=0 then concat(lower(u.firstname),'.',substring(lower(u.lastname),1,20),'001')

Else 

concat(lower(u.firstname),'.',substring(lower(u.lastname),1,20),(select substring_index(substring(u1.susystemusername,1,(length(u1.firstname)+length(substring(u1.lastname,1,20)),'@',1)+1 from users u1 where u1.systenusername like concat(u1.firstname,substring(u1.lastname,1,20))),'@abc.com') END

[rushikeshvartak]

Refer 

• https://forums.saviynt.com/t5/identity-governance/how-to-write-the-advance-query-to-generate-systemu...
• https://forums.saviynt.com/t5/community-knowledge-base/how-to-generate-a-random-three-letter-systemu...