Replace Unwanted Space in Post Content URL

Question

I have a Wordpress site with a couple hundred posts. The posts were being created with batch processing and a piece of code was being inserted dynamically. I found an error in the URL of one of the hyperlinks being inserted into the post content. Every URL has a space instead of the desired "-" character. I'm trying not to manually fix each post.

As you can see, part of the URL below has a space in it and is causing 404 errors in all of the different posts. SQL sees it like this:

<a href="http://www.mysite.com/this-URL-is-broken-because-of-a space"></a>
<a href="http://www.mysite.com/this-URL-is-broken-because-of-a space-also"></a>
<a href="http://www.mysite.com/this-URL-is-broken-because-of-a stupid-space"></a>

Google sees them like this:

<a href="http://www.mysite.com/this-URL-is-broken-because-of-a%20space"></a>
<a href="http://www.mysite.com/this-URL-is-broken-because-of-a%20space-also"></a>
<a href="http://www.mysite.com/this-URL-is-broken-because-of-a%20stupid-space"></a>

Unfortunately, as you can see, the URLs aren't the same, so I can't do a find an replace. I'm hoping someone has some savvy SQL operation to find the space and replace it with the appropriate "-" character. I'm thinking that I could say something like this:

Replace any blank space in the field with the "-" character, unless the blank space belongs to the "a href" string.

SQL Table -> wp_posts Field -> post_content

Maybe there's a .htaccess solution?

Thanks in advance for the help!

Answer 1

I think you should run this query. To replace space with dash.

update wp_posts set wp_posts.post_name = REPLACE( wp_posts.post_name, ' ', '-' );

Don't forget to change table_prefix in this. And also, keep a backup on current database before trying.