XML Special characters / MySQL

[johnd0e]

Hi !
I'm a novice to mysql and do not have any idea about XML. The database I'm working on has been imported (somehow) form a XML file. Now all special characters are displayed in some kind of code within the database. For example a small e with accent (`) is 	

Here are my questions:
1. What kind of code is that?
2. is there anyway to replace those characters with the actual special character? [ Otherwise all my cgi-scripts are worthless since they use the user name and definitely need the EXACT spelling of it..]


It would be great if anyone of you could help me.

Regards,
John

[TheDude]

Can you post examples of these characters as a plain text file on your server somewhere?

Most probably somewhere along the export/import process UTF-8 data got "translated" into ASCII code.
(most XML files are UTF-8)

There are some online sites where you can decode such strings, or at least download character equivalent charts, or you could use a good desktop editor (XML Spy, BBEdit), or write a regex function in PHP. You'd then copy tables (make backups), run the content through the transformer script, and put it back in mySQL again. Depending on the size of your data, PERL might be better suited.

[johnd0e]

well, the main problem is, that I do not know how to write a script tthat would do this.
And I do not feel like doing a replace for every special character in nearly 6000 records..(even though BBEdit is a great prog.)


May I email it to you?


(Btw, thx for your help..)


John

[TheDude]

May I email it to you?

No, thanks. As I said, upload it to a server, as a plain text file, preferrably zipped. Just a few samples, not all of the 6000 entries ;-)

Basically, you either:

change the content of your tables ONCE with a desktop-app (Edit Plus, HTML-Kit, BBEdit...)

or

ditto, but via PERL-/PHP-Script on the server

or

do a "replace" function when outputting from your existing db


By the way: sooner or later you WILL have to learn about regular expressions and replace functions. So why not start now?

Also check this:
http://www.php.net/manual/en/function.chr.php
http://www.mindspring.com/~jc1/seria...ces/ASCII.html
http://www.google.com/search?hl=en&q...php%20examples