mysql encoding issue with special character

Question

Hi,

I noticed my Rails app returns in place of double quotes and certain dashes, it returns a question mark character-- �

Is there a way to update the table and replace them with original characters?

Thanks!

Amie

Answer 1

update [table_name] set [field_name] = 
replace([field_name],'[string_to_find]','[string_to_replace]');

Should do the trick. Always run against a test database first until you know it won't break anything.

Use this statement at your own risk.

Answer 2

Those question marks indicate an invalid byte for the encoding of the environment the result is displayed in.

These days most environments will be utf-8.

If your running the mysql console app, run the following command when you first connect to tell the MySQL server to return all results for the current connection in utf-8:

  SET NAMES utf8;

In rails, you can have this command run when ActiveRecord opens a db connection by adding "encoding: utf8" to your database.yml file. You should also to make sure your webserver (apahce/nginx/etc) is sending the utf-8 HTTP header and that your HTML files have a Content-Type meta tag.

It's possible you have corrupt bytes stored in your database and this won't help. If that's the case, you're in for a world of fun trying to clean it up :)