I have numerous tables with some funky character replacement going on. I found some information on running an update to replace these, but it's in numerous tables and numerous fields.
update <table_name>
set <field> = replace(<field>,'’','\'');
update <table_name>
set <field>= replace(<field>,'…','...');
update <table_name>
set <field>= replace(<field>,'–','-');
update <table_name>
set <field>= replace(<field>,'“','"');
update <table_name>
set <field>= replace(<field>,'â€','"');
update <table_name>
set <field>= replace(<field>,'‘','\'');
update <table_name>
set <field>= replace(<field>,'•','-');
update <table_name>
set <field>= replace(<field>,'‡','c');
Do you all have any advice on running this across every field for all the tables? I thought about doing a mysql dump and using sed to replace everything, but I'm not sure how to structure the commands since they're all special characters.
Any help would be greatly appreciated.
Best Answer
You could try to query the table information_schema.columns and find every table in your MySQL DB Instance that have character fields. Here is the table's layout:
Here is a query to get every character-based column type for all user-defined tables:
You could join that query to a second query. The second query would have the name of every table with those column types:
Now, message the output to make it write a generic script for you:
Output should look something like this:
Last step for generic script generation is to output the query to a text file like this:
You can customize the generic script from there.