cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Replacing Multiple Special Character

Karan_Mhatre
New Contributor II

Hello Experts,

I have a source column (Emp_name) where many special character are there in name ,want to remove all the special characters.

Tried using replace and replaceAll but i didnโ€™t find a function that can take list of special character like below {!,@,#,$,%,^,&,*}.
Source data looks like these:-

Emp_id Name
100 Tom!@#$
200 Scott**&
300 Tig*!!@er
400 N!e@@el
500 #$Je%rry
600 James*&^%

I used to do these using convert function in IBM Datastage where is used to pass the list of special character and then replace it,but i didnt find any function here.

Can anyone guide me through how can i do it?

Appreciate your help.

3 REPLIES 3

Andrej_Bogdanov
New Contributor III

Hello,

You are correct that we canโ€™t list all of the special characters in the replaceAll function.
However you can use the replaceAll function to solve this issue, you just have to place the function as demonstrated in the example below:

On source I have a field with the name: $field with a value โ€˜Andrej%#@!โ€™. I am using the following expression:
$field.replaceAll(โ€˜%โ€™,โ€˜โ€™).replaceAll(โ€˜#โ€™,โ€˜โ€™).replaceAll(โ€˜@โ€™,โ€˜โ€™).replaceAll(โ€˜!โ€™,โ€˜โ€™).replaceAll(โ€˜*โ€™,โ€˜โ€™).
Even if you are missing one special character in the value and you do have it in the expression itโ€™s not an issue the expression will still work.
Example: I am missing the * special character in the value but i have it in the expression and it still works.
Let me know if this fixed the issue for you.

Regards,

@Andrej_Bogdanovski Thank you very much.

However i have done these in another way ,may be it will help you ,so know we know 2 ways to do these

  1. $Name.replace(/[^a-zA-z]/g,โ€œโ€).
  2. $Name.replaceAll(โ€˜!โ€™,โ€˜โ€™).replaceAll(โ€˜@โ€™,โ€˜โ€™).replaceAll(โ€˜#โ€™,โ€˜โ€™).replaceAll(โ€˜$โ€™,โ€˜โ€™).replaceAll(โ€˜%โ€™,โ€˜โ€™).replaceAll(โ€˜^โ€™,โ€˜โ€™).replaceAll(โ€˜&โ€™,โ€˜โ€™).replaceAll(โ€˜*โ€™,โ€˜โ€™)

Hope these help us.
Again thank you for your solution.

Note that this will replace numbers as well. If you want to keep the numbers, you can modify the expression to something like the following:

$Name.replace(/[^a-zA-Z0-9]/g, '')