How to escape special characters [',-,(,),_,] in create account Json for Database Connector?

[SKulkarni]

There is scenario where we need to escape special characters like [',-,(,),_,], while creating account in SQL Server application. During account creation we encounter values for First name/Last name/email/Display name are with the special characters as [',-,(,),_,].
Example:
First Name/Last Name: "Ha'reg, Ha-reg, Ha_reg ", 
Email: "ha'reg.abadi@companyname.com, ha'reg-abadi@companyname.com" ha_reg.abadi@companyname.com etc.

Can anyone help on this query to escape special characters in create account JSON for Database Connector?

[NM]

Hi @SKulkarni , use SQL replace function

REPLACE(user.firstname,''','\'')

[SKulkarni]

Thank you for the reply.

The mentioned expression did not work for me. Also, we need it for all special characters like [',-,(,),_,].

[NM]

Hi @SKulkarni , if you try it for a user without any special characters does that work? As in does provision work?

[SKulkarni]

Hi @NM

Yes. it works fine without special character.

[rushikeshvartak]

${user.firstname.replaceAll("-", "").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("_", "").replaceAll("'", "")}

[Raghu]

@SKulkarni  share account json please

[SKulkarni]

Hi,

Please find the attached JSON.

[rushikeshvartak]

{
  "UpdateAccountQry": [
    "EXEC MVS.proven.view_updateuser '${user.customproperty5}', '${user.customproperty2}', '${user.customproperty1 == null ? user.lastname : user.customproperty1}', '${user.preferedFirstName == null ? user.firstname : user.preferedFirstName}', '${user.middlename == null ? '' : user.middlename}', '${user.email == null || user.customproperty7 == 'N' ? '' : user.email.replaceAll(\"-\", \"\").replaceAll(\"\\\\(\", \"\").replaceAll(\"\\\\)\", \"\").replaceAll(\"_\", \"\").replaceAll(\"'\", \"\")}', '${user.customproperty20 == 'Leave of Absence' ? -9 : (user.customproperty20 == 'Returned From LOA' ? 1 : 1)}', NULL, '${user.employeeid == null ? '' : user.employeeid}', '${user.username}', NULL, '${user.customproperty8 == null ? '' : user.customproperty8}', '${user.employeeType == '8888' && user?.customproperty27 != null ? user?.customproperty27 : ''}', 'ENGINE', NULL, '${task?.id}', NULL, NULL"
  ]
}

[SKulkarni]

Thank you for reply.

The suggested JSON is not working and we got the following error message, "Failed to parse template script (your template may contain an error or be trying to use expressions not currently supported)"

[rushikeshvartak]

Is it working without replaceAll logic ?