Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Trademark and Register Signs

andrew
5 - Atom
How do you get rid of special characters like ™ and ® when you are preparing data from Excel for MySQL. I tried using a character replace but it doesn't seem to pick them up. Are there other tool options or do I need to convert the Excel file to something else?

Thanks,
Andrew
3 REPLIES 3
tom_montpool
12 - Quasar
I'm assuming that the symbols that you want to remove are in a field called "Test".

You could use a regular expression to remove "all non-ASCII" characters -- so you have to find out how UNICODE specifies the ASCII characters -- see http://en.wikipedia.org/wiki/List_of_Unicode_characters#Basic_Latin

REPLACE(REGEX_REPLACE([Test],"(.*)(?:[^x20-x7E])(.*)","$1"+"$2"),"  "," ")

The key in this is the (?:[^x20-x7E]) -- basically this is making:
  1. a non-marked group -- the ?: part
  2. a 'character set' -- the x20-x7E part
  3. and it is negating the character set -- the ^ part

So the REGEX_REPLACE is saying "From the Test field, give me everything (.*) until you find something that's not ASCII (?:[^x20-x7E]) and then give me everything else (.*) and put part one $1 and part two $2 together into a new field".

For some reason, the (tm) seems to leave an extra space between the words, so I included a REPLACE that changes two spaces into one.

vkarthik21
8 - Asteroid

The above expression isn't working for me. Here is my input - nbn™ 12 and I am expecting to get nbn 12. It's not happening. I am not sure if I should be replying here or opening a new thread.

Erik_Schaap1
6 - Meteoroid

Two backslashes are missing in the formula: [^x20-x7E] should be replaced by [^\x20-\x7E]

Labels