Remove accented characters while generationg Email and Systemusername

[sppnra]

Hi Saviynt Team,

I need to remove accented character while generating email and systemusername I have written the below logic. Its working for the first user when I m uploading but not working when I m trying to upload the second with the same firstname and lastname then it should go to the second use case and then show on.

concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('@test.com
',1,17)) ### CASE WHEN (users.middlename is NOT NULL) THEN concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , substring('users.middlename',1,1) , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('@test.com
',1,17)) END ### concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('2',1,1) , substring('@test.com
',1,17)) ### concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('3',1,1) , substring('@test.com
',1,17)) ### concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('4',1,1) , substring('@test.com
',1,17)) ### concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('5',1,1) , substring('@test.com
',1,17)) ### concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.firstname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z") , substring('.',1,1) , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(users.lastname,"-","")," ",""),"'",""),"Ą","A"),"ą","a"),"Ć","C"),"ć","c"),"Ę","E"),"ę","e"),"Ł","L"),"ł","l"),"Ń","N"),"ń","n"),"Ó","O"),"ó","o"),"Ś","S"),"ś","s"),"Ź","Z"),"ź","z"),"Ż","Z"),"ż","z"), substring('6',1,1) , substring('@test.com
',1,17))

Can you let me know what can be the issue.

[Manu269]

@sppnra 

You can leverage the database functions FN_EIC_REPLACE for the same

Check these post :

Use of custom DB function to replace the ascend ch... - Saviynt Forums - 27289

How we can handle special character in System User... - Saviynt Forums - 47458

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter02-Identity-Repository/Dat....

This DB function works in conjunction with the REPLACE_MAPPING dataset provided by Saviynt, which contains default alternatives for replacing these letters and characters.