Can't remove   generated by editor from database

Question

I have found out that when i save post, there are "kind of" non breaking space saved, sometimes. I don't mind "regular  ". This can appear anywhere in the text.

In the editor, they are obviously not visible.

=> if i use the editor source tool, the space looks like regular space.

The problem is : on the site, they don't apprear so it's like the two words are without space at all.

If i check the source, i see   for these spaces.

I have already a lot of data saved, so i want to remove the nbspace with query :

I tried :

UPDATE `vss_posts` SET `post_content` = replace(`post_content`, " ", " ");

But it did nothing : the nbsp where still there.

i've seen this post talking about hexa nbsp;

But obviously,

UPDATE `vss_posts` SET `post_content` = replace(`post_content`, "\xc2\xa0", " ");

doesn't work.

What could be the problem ? How can i remove them in the db ? How can i prevent them to be saved again ?

In the aforementionned post i saw this :

add_filter( 'wp_insert_post_data', 'rm_wp_insert_post_data', '99', 2 );
function rm_wp_insert_post_data ( $data , $postarr ) {
   return str_replace("\xc2\xa0", " ", $data); 
}

Does it apply to my problem ? (just for preventing, not for curing)

The only way i've found sofar is to manually edit the post, remove the space and recreate it with the space tab.

Answer 1

Non-breaking spaces are often added because they are present in word processing docs and then copied into the WP editor. I find the easiest way to remove them from a single post, including any unwanted formatting that results from copying text from a Word doc, is to copy the text (in visual editor mode) where they are contained into a text editor (thus stripping all formatting) and then copying back into the visual editor.

To remove them from all database content I would export the SQL database using a database management tool. I use phpMySQL to do this - select the table and click export button, set a compression format and also set it to include the command to drop tables on import so the import replaces the existing data with the new when reloading the DB. That way you get to extract the file from the downloaded zipped export so you have an archived backup to fall back on if something screws up.

Then, using a text editor, do a find on   and replace with a space, and re-import the edited SQL database. It only takes a few minutes.