SQL Search and Replace in Drupal 8 Body Field

ou 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.