Wierd characteres after transfer of data

[Logical1]

We transfered our site and its MYSQL database from one hosting company to another one and in doing so I made backups of old database and exported it to the new one.

Now in each record (which are all text) a lot of wierd characters are showing up like:

13 – 17 Seconds

or:

Temperature -5ºC

 

Why is this and how can it be fixed.  There are some 1200 row of data and each has over 30 columns and removing them manually will take a very long time.

Any suggestions is appreciated.

L1

[ldougherty]

How did you export the data and import to the new server?

 

Did you create a database dump and import it?  Have you checked that the collations are the same for both database servers?

 

Try looking in your dump file itself (assuming you have one) and see if the odd characters exist in the file itself. 

 

If they do its either your dump or the editor you are using.

 

If they do not then its the method of importing into the new database such as the collation.

[Logical1]

Thanks for your reply.

The backup wa made with phpMySql's back up tool.

It seems like the characters are present even in the back up file. 

I there a way to do a character search an replace on tables in MySQL?That seems to be the quickest way to solve the problem since the characters are always before degree centigrade sign and ' and bullets so they can easily be identified.

Thanks in advance

L1

[gevensen]

you can write a routine to read each row then use php to split the string into an array and reassemble the string without the offending characters and rewrite it in the place it came from ( for ex rec 1 2 3 ect )

 

look at the explode example http://www.php.net/manual/en/function.explode.php

 

use the mysql_query to select the record

 

mysql_fetch_array to read the row

 

write a php routine to remove the characters

 

then use mysql_query to update the record your just read