Trademark and Register Signs

[andrew]

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

[tom_montpool · Accepted solution]

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]

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]

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