Removing smart apostrophes from a database

Removing smart apostrophes from a database

How-to remove smart apostrophes in the PHPMyAdmin database.

Recently migrated a website from FlyWheel hosting to WPEngine. During the migration process, thousands of broken characters started displaying on the migrated site.

Better search and replace and search regex weren't able to find and replace the broken characters that looked like question marks (Unicode text). In WordPress's back end, when I edited the post, all I saw were question marks.

My solution was to go to phpMYAdmin and do a search and replace using the SQL tab.

Steps to how I fixed it

  1. Spellcheck the website with ScreamingFrog
  2. In the spellcheck results tab, pick out a page with Unicode errors
  3. Edit that page in WordPress, grabbing the page ID
  4. In PHPmyAdmin, locate that page ID under wp_posts
  5. Press edit, and copy 'n paste the error character/word into the SQL formula embedded below
  6. Add the desired database safe character in the second section. You can use a slash to escape the inner apostrophe. e.g. 'can/'t'
  7. Press go, repeat the process until all errors have been fixed.

SQL code: