Clean up weird characters in your WordPress posts

I just recently moved a large site to a new server. The database was over 300 mb so I used a nifty tool I found years ago to split up the database into smaller pieces to make importing easier.

Everything went fine with the import except there were lots of weird characters in many of the posts. Since these nasty little characters are caused by character set issues, I made a few adjustments and then used a plugin that I found to convert all the tables to UTF-8 called Convert WP Database to UTF-8. That got rid of most of my weird characters but I was still left with hundreds of posts that had the dreaded “black diamond question mark” symbol in them. It looks like this: �

Scary, isn’t it?

If it had just been a few posts, I would have just edited them and gone about my merry way. But edit over 400 posts? No way.

My Google searches came up with a great post on dig.wp (one of my favorite sites) about cleaning up weird characters in a database

Bingo! Exactly what I was looking for.

If you are plagued by those ugly little black diamond question marks, here’s how to send them packing. Run the following query on your database (instructions for using phpMyAdmin follow).

UPDATE wp_posts SET post_content = REPLACE(post_content, '�', ''); 

What I’m doing with this query is replacing the black diamond question mark with just a blank space. If you want to insert something in place of the black diamond question mark, you would put it between the single quotation marks.

A few important notes:

1. The query above works for a default WordPress install where the table prefix is wp_ . If your table prefix is different (and it should be), then replace wp_ with the appropriate prefix.

2. Make sure to take a backup of your database before doing this. You never know.

To run this query in phpMyAdmin:
1. Click on your database name, on the left.

phpmyadmin screen shot 1

2. Click on the SQL tab in the top right area.

3. Enter the query in the big white box there and then click the little oval “go” button in the lower right of the screen.

phpmyadmin screenshot 2

You should receive a message shortly that the query ran successfully.

I hope this solves your dreaded dark diamond question mark problems. :) I think this is a great way to quickly clean up a messy database without pulling out most of your hair.

Leave your comment below

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>