Trademark and Register Signs

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

Parse

Database Connection

Accepted answers

tom_montpool

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:

a non-marked group -- the ?: part
a 'character set' -- the x20-x7E part
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.

All comments

tom_montpool

a non-marked group -- the ?: part
a 'character set' -- the x20-x7E part
and it is negating the character set -- the ^ part

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335