SQL Search and Replace in Drupal 8 Body Field

Created on August 1, 2017 by Jeremiah John
Tags: SQL, Search and Replace, Drupal 8
 

You never know when you're going to encounter data ravaged by clients, cast aside and mauled, an antelope passed by lions to jackals.

But fortunately, data is much easier to repair than a gaping wound.

Here's the SQL query I'd use to replace non-breaking spaces with regular spaces. You'll need to replace it with whatever your search term is:

UPDATE node_revision__body nr
set nr.body_value =
replace(nr.body_value, ' ', ' ')
WHERE nr.body_value like '% %';
 
UPDATE node__body nb
set nb.body_value =
replace(nb.body_value, ' ', ' ')
WHERE nb.body_value like '% %';

Here's an SQL query to replace the www-prefixed domain with the non-www-prefixed domain:

UPDATE node_revision__body nr
set nr.body_value =
replace(nr.body_value, 'http://www.mydomain.org', 'http://mydomain.org')
WHERE nr.body_value like '%http://www.mydomain.org%';
 
UPDATE node__body nb
set nb.body_value =
replace(nb.body_value, 'http://www.mydomain.org', 'http://mydomain.org')
WHERE nb.body_value like '%http://www.mydomain.org%';

The easiest way to run these is by pasting them into the SQL CLI resulting from drush sqlc.

About the Author

Hi. My name is Jeremiah John. I'm a sf/f writer and activist.

I just completed a dystopian science fiction novel. I run a website which I created that connects farms with churches, mosques, and synagogues to buy fresh vegetables directly and distribute them on a sliding scale to those in need.

In 2003, I spent six months in prison for civil disobedience while working to close the School of the Americas, converting to Christianity, as one does, while I was in the clink.