How can I remove all special characters and spaces from values in a field?

NathanDorsch
NathanDorsch Member
edited November 2022 in Magic ETL

I want to remove all special characters and spaces from a field in ETL.... how can I do this? Is there an easier way than having to create an individual replace text line for each specific character?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Yes as it's not in the exclusion list

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Regular expressions are your friend in this case. You could ignore any non-alphanumeric characters in your string with something like this with a formula tile:

    REGEXP_REPLACE(`field`, '[^a-zA-Z0-9]', '')


    [] = Group of possible characters

    ^ = NOT

    a-zA-Z any letter

    0-9 = any digit

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    Thank you. Would that also remove spaces? so '10 / 20' would be '1020'

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Yes as it's not in the exclusion list

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    Thanks!

Categories