List:General Discussion« Previous MessageNext Message »
From:hsv Date:September 27 2012 9:24pm
Subject:Re: Need Help Converting Character Sets
View as plain text  
>>>> 2012/09/24 16:28 -0700, Mark Phillips >>>>
I have a table, Articles, of news articles (in English) with three text
columns for the intro, body, and caption. The data came from a web page,
and the content was cut and pasted from other sources. I am finding that
there are some non utf-8 characters in these three text columns. I would
like to (1) convert these text fields to be strict utf-8 and then (2) fix
the input page to keep all new submissions utf-8.

91) For the first step, fixing the current database, I tried:

update Articles set body = CONVERT(body USING ASCII);

However, when I checked one of the articles I found an apostrophe had been
converted into a question mark. (FWIW, the apostrophe was one of those
offending non utf-8 characters):

Before conversion: "I stepped into the observatory
After conversion: "I stepped into the observatory?s control room..."

Is there a better way to accomplish my first goal, without reading each
article and manually making the changes? 
I do not remember where on the MySQL website this is, but there was an article about
converting from character sets in version 4 to those in version 5, when UTF-8 first was
supported. It sounds to me that maybe the tricks shown there would be useful to you,
since, in effect, through MySQL MySQL was fooled into accepting for UTF-8 that which was
not. Conversion to binary string was mentioned.

Need Help Converting Character SetsMark Phillips24 Sep
  • RE: Need Help Converting Character SetsRick James24 Sep
    • Re: Need Help Converting Character SetsDerek Downey28 Sep
  • Re: Need Help Converting Character Setshsv28 Sep
    • RE: Need Help Converting Character SetsRick James28 Sep
      • Re: Need Help Converting Character SetsMark Phillips30 Sep
        • RE: Need Help Converting Character SetsRick James1 Oct
        • Re: Need Help Converting Character Setshsv2 Oct