Convert BLOB into TEXT with foreign characters

Question

I have a blob which holds HTML in it. Some of this HTML has foreign characters (ie: æ, ø, etc). I want to convert the BLOB into TEXT. I am using latin1 for my tables and changing this is not an option. From what I can tell, if if I store the foreign characters in TEXT, it is converted into something like ASCII. But if you store it in BLOB, it doesn't, it stays as the foreign character.

So, how can I convert this BLOB data into TEXT adn convert the BLOBL data into the ASCII like versions for the special characters?

Answer 1

Well, your blob holds binary data... so if it's got text (HTML) in it, that text must have been encoded somehow. What encoding is it in?

You'll need to decode the text and then let mysql re-encode it as Latin-1. That will preserve some non-ASCII characters... but there are plenty of characters which aren't part of Latin-1. You simply won't be able to represent those correctly in Latin-1. If changing that is not an option, then accurately storing all Unicode characters as text isn't an option either.