Replace invalid characters in MySQL


#1

I am trying to import from my previous host, however, it is littered with invalid characters.

Is there a SQL command that I can use to get rid of these?

Thank you,
-Zach Lym


#2

Is the CHARSET on your tables utf8?


#3

You shouldn’t try to get rid of those invalid characters. You will lose a lot of data.

I assume that you have dumped your database to a file from your old host. The characters are invalid in your new host because the character encoding of the file and the new database DO NOT match.

I’ll suggest you to double check the character encoding and make sure the database is dumped to a file with the correct encoding. Once you sort this out, you can easily import the file to your new database without any invalid character.


#4

Ahh no, I am not going to dump the old data or change the DNS info until I get the new site running smoothly : )

Excuse my ignorance, from my readings I assumed that MySQL replaced the unknown character with the UTF replacement character glyph (U+FFFD, �) and that I could simply delete this character. It appears to be wherever a double space is (like after a period)…

I have successfully exported this db from my old host to my personal host (mediatemple) with no problem. There is a mix of latin1_swedish_ci, utf8_unicode_ci, and utf8_general_ci encodings (I am assume that’s what phpmyadmin means by “Collation”) BUT they all match up between the old and new databases, i.e. the table in on DB has the same Collation value as the table in the new DB.

Any suggestions?