Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Replace Qualifiers/prefix/suffixes from Name during Import

sdey_2023
Regular Contributor
Regular Contributor

Hi Team,

We have a requirement where we need to replace any qualifiers from the names of imported Users like MR. , JR. , Sr. , II, IV, X which mostly appear as prefix or suffix of names.

e.g - Robert Viv IV should convert to Rober Viv,

         Downey JR. should convert to Downey

Is there any way to achieve this during import via preprocessor/Modify JSON?

14 REPLIES 14

rushikeshvartak
All-Star
All-Star

You can update same 

UPDATE users SET name = REGEXP_REPLACE(name, '(^|\s)(MR\.|JR\.|Sr\.|II|IV|X)(\s|$)', ' ')


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Tried the query but it is failing with error, added in comment the details.

sdey_2023
Regular Contributor
Regular Contributor

Can we nest REGEXP_REPLACE with REPLACE

Hi @sdey_2023 ,
 
{
    "ADDITIONALTABLES": {
        "USERS": "SELECT USERNAME,FIRSTNAME,LASTNAME FROM USERS"
    },
    "COMPUTEDCOLUMNS": 
[ "USERNAME"
],
    "PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET NEWUSERDATA.username = REGEXP_REPLACE(NEWUSERDATA.username, '(^|\s)(MR\.|JR\.|Sr\.|II|IV|X)(\s|$)', ' ')"
    ]
}

@rushikeshvartak Getting the following error when trying to import:

uploadUserFinalStep.returnMap=[Creating-Temp-Table-CURRENTUSERS-time:313 ms, Failure_msg:Error in Users Import - Error while processing data: FUNCTION ssminlp.REGEXP_REPLACE does not exist, Dropping-Temp-Table-NEWUSERDATA-time:9 ms, Dropping-Temp-Table-CURRENTUSERS-time:11 ms, Total_Preprocessing_Time:407 ms, Existing_Users:5555, Number_of_Records_in_Feed:1, Users_In_Feed:0, Active_Users_not_in_feed:5555, Users_Inserted:0, Users_Updated:0, Users_Inactivated:0, Users_Count:0, Total_Import_Time:2231 ms, Success:false]

@sdey_2023 ,

I tested this using file import, it worked.

Can you try below:

 

{
    "ADDITIONALTABLES": {
        "USERS": "SELECT USERNAME,FIRSTNAME,LASTNAME FROM USERS"
    },
    "COMPUTEDCOLUMNS": 
	[ 	"USERNAME"
	],
    "PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET NEWUSERDATA.lastname = REGEXP_REPLACE(NEWUSERDATA.lastname, '(^MR.|JR.|Sr.|II|IV|X)(s|$)', ' ')"
    ]
}

 

PremMahadikar_0-1713909239900.png

 

Test using file import as suggested by @PremMahadikar  and let us know if you face issues


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

sdey_2023
Regular Contributor
Regular Contributor

@PremMahadikar  @rushikeshvartak  tried using csv file import only, but getting this error:

Error while processing data: FUNCTION ssminlp.REGEXP_REPLACE does not exist

We are using v24.2 of Saviynt.

Do we need to import or enable any function/table for using REGEXP_REPLACE

No


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

sdey_2023
Regular Contributor
Regular Contributor

This is the detailed error message:

java.sql.SQLSyntaxErrorException: FUNCTION ssminlp.REGEXP_REPLACE does not exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8698) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1716) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

Its issue with your csv file


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

sdey_2023
Regular Contributor
Regular Contributor

I am using below csv:

sdey_2023_0-1713939254592.png

 

@sdey_2023 , Please try below attached file.

  • Have username column
  • PremMahadikar_0-1713939822371.png
  • Map the columns during preview (username, firstname,lastname, statuskey)

 

Unfortunately, it is the same error. Should we raise a ticket with Saviynt?