Replacing Multiple Special Character

[Karan_Mhatre]

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.

[Andrej_Bogdanov]

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,

[Karan_Mhatre]

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

[tstack]

Karan_Mhatre:

$Name.replace(/[^a-zA-z]/g,“”)

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