BOLDelite

Importing WordPress Database With Special Characters

Posted November 25, 2015 · 1 min read

If you've built any number of WordPress websites and had to move the database to a new server, it's likely that you've run into a situation where the import process breaks and your new site looks like a bowl of scrambled eggs. More often than not, this break is caused by a special character, most often copied in from a Word document or another rich text editor.

The downside is that this can be a time consuming problem to fix, depending on how deep you've gotten yourself. The upside is that a simple preventative step can be taken to ensure your database plays nice.

Disclaimer: this can be a very delicate operation and, if done incorrectly, can create irreparable damage to your website. Always create a backup of your database before performing a query like this, and if you feel like you're in over your head, it would be worth the money to pay a professional to migrate your database. It takes a short time and could save you hours of headache. We are not liable for any damage you do to your database.

Disclaimer aside, there are admittedly more graceful ways to write the query below, but it's been laid out in a way that it can be most easily understood. If you have any questions, please contact us for assistance.

SQL Query to Cleanse Your WordPress Database of Special Characters

UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '"', '“');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '"', '”');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, ''', '’');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '‘', '‘');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '–', '–');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '—', '—');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '…', '…');

UPDATE wp_options SET option_value = REPLACE(option_value, '"', '“');
UPDATE wp_options SET option_value = REPLACE(option_value, '"', '”');
UPDATE wp_options SET option_value = REPLACE(option_value, ''', '’');
UPDATE wp_options SET option_value = REPLACE(option_value, '‘', '‘');
UPDATE wp_options SET option_value = REPLACE(option_value, '–', '–');
UPDATE wp_options SET option_value = REPLACE(option_value, '—', '—');
UPDATE wp_options SET option_value = REPLACE(option_value, '…', '…');

UPDATE wp_posts SET post_content = REPLACE(post_content, '"', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '"', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, ''', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');

UPDATE wp_posts SET post_title = REPLACE(post_title, '"', '“');
UPDATE wp_posts SET post_title = REPLACE(post_title, '"', '”');
UPDATE wp_posts SET post_title = REPLACE(post_title, ''', '’');
UPDATE wp_posts SET post_title = REPLACE(post_title, '‘', '‘');
UPDATE wp_posts SET post_title = REPLACE(post_title, '–', '–');
UPDATE wp_posts SET post_title = REPLACE(post_title, '—', '—');
UPDATE wp_posts SET post_title = REPLACE(post_title, '…', '…');

UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '"', '“');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '"', '”');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, ''', '’');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '‘', '‘');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '–', '–');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '—', '—');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '…', '…');

Why so basic?

This simplistic website reflects our values of development: to create a fast, clean, custom, accessible, and secure internet.