Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

replace apostrophe and period character in lastname

Abdul_Gaffar
New Contributor II
New Contributor II

Hi ,

We are getting names from source as for instance ( Fernoldjr. , O'connell etc.) which then creates email as in firstname.fernoldjr..com 

To escape the special characters (',.) below is the preprocessor I was trying however , I was getting syntax error

Query:

{
"ADDITIONALTABLES": {
"USERS": "SELECT EMPLOYEEID, systemusername"},
"COMPUTEDCOLUMNS": [
"customproperty20","lastname"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA
SET NEWUSERDATA.lastname = REPLACE(REPLACE(currentusers.lastname, ''', ''), '.', '')
WHERE currentusers.lastname LIKE '%'%' OR currentusers.lastname LIKE '%.%'"]
}

Error :

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.', '') WHERE lastname LIKE '%'%' OR lastname LIKE '%.%'' at line 1 

please let me know if am missing anything as per syntax perspective

 

Thanks 

Abdul Gaffar

2 REPLIES 2

Saathvik
All-Star
All-Star

@Abdul_Gaffar : Use below query

SET NEWUSERDATA.lastname = REPLACE(REPLACE(currentusers.lastname, ''', ''), '.', '')
WHERE currentusers.lastname LIKE '%\'%' OR currentusers.lastname LIKE '%.%'"


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rushikeshvartak
All-Star
All-Star

{
"ADDITIONALTABLES": {
"USERS": "SELECT EMPLOYEEID, systemusername"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"lastname"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.lastname = REPLACE(REPLACE(NEWUSERDATA.lastname, '.', ''), '''', '')"
]
}


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.