reverse mutliple charset convert
f-...@laposte.net
One of my table got messed up on the "title" column.
For example the french word "frère" is now written as "frère".
I guess what happened was:
1) select convert(_latin1 "frère" using utf8)
--> frère
2) select convert(_latin1 "frère" using utf8)
--> frères
My problem is that I couldn't get it backward by doing :
select convert(_utf8 "frère" using latin1)
because it still ends up with the same result: frères
Can someone help me with this, and maybe give me the command to change
the whole "title" column.
Thank you very much for any help
François
lark
can you with certainty say what happened? also let us know your current
encoding on the table and current collation on its columns? also let us
know what you want it converted to.
f-...@laposte.net
to give you a straight forward answer,
charset and collation are latin1 and latin1_bin
I have tried all the changes on my column but not change was visible
My question is thus:
if **select convert(_latin1 "frère" using utf8)** --> frère
what will reverse this?
Thanks a lot for any hint.
François
lark
i don't think you can reverse this. i think you can try to convert it to
some other character set and collation combination!
lark
Willem Bogaerts
>
> For example the french word "frère" is now written as "frère".
>
> I guess what happened was:
>
> 1) select convert(_latin1 "frère" using utf8)
> --> frère
> 2) select convert(_latin1 "frère" using utf8)
> --> frères
It could also be that you sent utf-8 data on a latin-1 connection. To be
sure what encoding is used, send the following command:
SHOW VARIABLES LIKE '%char%';
If it is a combination of a web site and MySQL that put the data there,
the encoding of the site may be utf-8, causing browsers to send utf-8 data.
> My problem is that I couldn't get it backward by doing :
>
> select convert(_utf8 "frère" using latin1)
This is a tough one. what you see is no longer what you get. It all
depends of the encoding used. In what encoding is your string sent to
the server? And how do you send it (using which program)?
Best regards,
--
Willem Bogaerts
Application smith
Kratz B.V.
http://www.kratz.nl/
f-...@laposte.net
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> This is a tough one. what you see is no longer what you get. It all
> depends of the encoding used. In what encoding is your string sent to
> the server? And how do you send it (using which program)?
>
Actually I use either a firefox webbrowser and a phpmyadmin, usually
in utf8.
But I also try a few things directly on the server (apache2) through a
dos command line on XPsp2.
There must be a way of getting this right again :-(
The problem is not the new entries, because everything is ok for the
newly created entries. The problem is that I have got a whole column
with corrupted datas on every accented letter (and believe me, in
french, there are many accents).
François
f-...@laposte.net
I found a very simple solution which I immediately published on my
site:
http://www.fxparlant.net/Mysql_Charset
I have noticed that the result of the double conversion ends with the
two characters 'ƒÂ' being added in every accented code:
é -> é
è -> è
ê -> ê
î -> î
At least, this works for french characters, I'm not sure it is the
same for german characters (Ö or ß), nor for asian characters
Therefore, I simply made an update query on the columns where the
problem happens. I simply erase the 'ƒÂ' characters.
UPDATE page SET `page_title` = REPLACE(page_title,'ƒÂ','');
UPDATE pagelinks SET `pl_title` = REPLACE(pl_title,'ƒÂ','');
It worked fine for me, but of course suche queries should be tested
first on a bigger scale.
Thank you for your help, and hope this helps.
François
Joachim Durchholz
> I have noticed that the result of the double conversion ends with the
> two characters 'ƒÂ' being added in every accented code:
>
> same for german characters (Ö or ß), nor for asian characters
If it solves your problem: great!
It won't work in general, though.
> Therefore, I simply made an update query on the columns where the
> problem happens. I simply erase the 'ƒÂ' characters.
>
> UPDATE page SET `page_title` = REPLACE(page_title,'ƒÂ','');
> UPDATE pagelinks SET `pl_title` = REPLACE(pl_title,'ƒÂ','');
In German, we'd probably have something like
UPDATE page SET `page_title` = REPLACE(page_title,'ÃÂ','Ö');
This kind of mapping will always work (with the "right" character sets
in place), because UTF8-to-anything-else is never ambiguous (at least
not for the character sets that are covered by UTF).
Regards,
Jo
f-...@laposte.net
Sorry Jaochim, you are right in the fact that an easy update won't
work for german, but I think you missed the point of making a
**double** conversion.
What the table below shows is that there is an easy rule for small
letter with an ¨ (umlaut), but no rule for capital letters nor for ß.
Capitals:
A A A
E E E
I I I
O O O
U U U
small:
a a a
e e e
i i i
o o o
u u u
Capital¨:
Ä Ã„ Ä
Ë Ã‹ Ë
Ï Ã ÃƒÂ
Ö Ã– Ö
Ü Ãœ Ãœ
small¨:
ä ä ä
ë ë ë
ï ï ï
ö ö ö
ü ü ü
ss:
ß ÃŸ ß
Sincerely
François
lark
> Hi,
>
> Sorry Jaochim, you are right in the fact that an easy update won't
> work for german, but I think you missed the point of making a
> **double** conversion.
>
> What the table below shows is that there is an easy rule for small
>
>
> Capitals:
> A A A
> E E E
> I I I
> O O O
> U U U
>
> small:
> a a a
> e e e
> i i i
> o o o
> u u u
>
> Ä Ã„ Ä
> Ë Ã‹ Ë
> à àÃÂÂ
> Ö Ã– Ö
> Ü Ãœ Ãœ
>
> small¨:
> ä ä ä
> ë ë ë
> ï ï ï
> ö ö ö
> ü ü ü
>
> ss:
> ß ÃŸ ß
>
> Sincerely
>
> François
Francois,
to repair a table's character set and/or collation, there is an option
for myisamchk that you can run on the table at the file level. it looks
something like this:
myisamchk --set-collation=utf8_bin
--
lark -- ham...@sbcdeglobalspam.net
To reply to me directly, delete "despam".
f-...@laposte.net
I didn't know of this command. I'll try to use it next time I update,
because I really think my backup always gets this wrong.
Thanks for the tip.
François