Replace Qualifiers/prefix/suffixes from Name during Import

[sdey_2023]

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?

[rushikeshvartak]

You can update same 

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

[sdey_2023]

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

[sdey_2023]

Can we nest REGEXP_REPLACE with REPLACE

[PremMahadikar]

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|$)', ' ')"

    ]

}

[sdey_2023]

@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]

[PremMahadikar]

@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|$)', ' ')"
    ]
}

[rushikeshvartak]

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

[sdey_2023]

@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

[rushikeshvartak]

No

[sdey_2023]

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)

[rushikeshvartak]

Its issue with your csv file

[sdey_2023]

I am using below csv:

[PremMahadikar]

@sdey_2023 , Please try below attached file.

• Have username column
• 
• Map the columns during preview (username, firstname,lastname, statuskey)

[sdey_2023]

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