Forum Discussion

Karan_Mhatre's avatar
Karan_Mhatre
New Contributor II
6 years ago

Replacing Multiple Special Character

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.

mapper

3 Replies

  • Andrej_Bogdanov's avatar
    Andrej_Bogdanov
    New Contributor III
    6 years ago

    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's avatar
      Karan_Mhatre
      New Contributor II
      6 years ago

      @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's avatar
        tstack
        Former Employee
        6 years ago

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