Mysql – Replace text with spaces in MySQL

MySQLreplaceWordpress

I'm trying to do a global replace of search in my database, which has a lot of articles with a double carriage return because of this code:

<p> </p>

I'd like to replace this in my WordPress blog so instead of that appears… nothing, and so I can delete the CR. I've tried this on my database

UPDATE wp_posts set post_content = replace (post_content,'<p> </p>','');

but didn't work. Why? Do I have to add special thinks to consider the space between the <p>and the</p>?

Mmm. Good points, both Jon Angliss and Wim. Jon, as you could have guessed, the database shows no entries with that text string. So there's something going on inside the post_content field.

Wim, the famous   was replaced previously, but there are still hundreds of posts that for some reason have something different between the p and the /p tags.

I've done a search of one of the posts with this error:

mysql> select *  from wp_posts where post_title like '%3DVisionLive%';

And looking in the wp_content field, this is a little piece of the post:

Phil Eisler, responsable de la divisi?n 3D Vision.?</p>
<p>?</p>
<p>Este portal ser? por tanto

No spanish tilde (accent) shown on the terminal, and instead of an space there's a quotation mark between the p and the /p tags. I've tried to replace <p>?</p>, but again, no results. There's some character (or several) there, but I don't know how to discover that. Maybe it's the character set of my terminal, but I've accessed the database from phpmyadmin and in that case there's a space character between the p and the /p. Weird.

Well, there's more info about this After trying several things, I've made this replace:

UPDATE wp_posts set post_content = replace (post_content,'&nbsp;',' ');

and now in my articles appear a 

<p> </p>

instead of the previous   string that was causing an extra CR between paragraphs that looks really useless and poor. so I'm back at the beginning. What I need to do is to replace   with a null character or just a space.

Could it have to be with the ampersand, and in that case, should I use something like &nbsp; or something similar?

Mmm. Good points, both Jon Angliss and Wim. Jon, as you could have guessed, the database shows no entries with that text string. So there's something going on inside the post_content field.

Wim, the famous   was replaced previously, but there are still hundreds of posts that for some reason have something different between the p and the /p tags.

I've done a search of one of the posts with this error:

mysql> select *  from wp_posts where post_title like '%3DVisionLive%';

And looking in the wp_content field, this is a little piece of the post:

Phil Eisler, responsable de la divisi?n 3D Vision.?</p>
<p>?</p>
<p>Este portal ser? por tanto

No spanish tilde (accent) shown on the terminal, and instead of an space there's a quotation mark between the p and the /p tags. I've tried to replace <p>?</p>, but again, no results. There's some character (or several) there, but I don't know how to discover that. Maybe it's the character set of my terminal, but I've accessed the database from phpmyadmin and in that case there's a space character between the p and the /p. Weird.

Best Answer

My first check would be to see if what is in the DB is what you really think it is. For example, do you really have posts that have the exact string you're using? Have you tried doing a select to see if that string matches?

select ID from wp_posts where post_content like '%<p> </p>%'

If you don't get any responses back, then your string doesn't match exactly, and you'll want to start finding out what's really in the string, maybe export to a file, and look at the content there. Also, don't forget the REPLACE function is case sensitive, so <P> is not the same as <p>.

Related Topic