How to replace HTML tags and text in a Drupal database using phpMyAdmin and SQL

Did you validate your drupal website and realize you need to replace some unclosed HTML4 tags with XHTML strict self-closing tags? Or maybe you're adding in some ARIA roles to make your text a bit more accessible.

Lo and behold: you will suddenly need to use your web host's phpmyadmin interface and SQL to find and replace strings of text in drupal's database.

Don't panic! Here's how you do it.

Step 1: Backup the table you'll be fiddling with

First, download a backup of the node_revisions table, as this is where the body field of each of your nodes lives.

If the text you're concerned about lives in a different field than the body, you'll need to adjust these instructions to deal with that field (which may be in a different table). For instance, you may also need to fiddle with your teaser field.

Step 2: Use a SELECT query to find out the number of nodes that will be affected

Second, get a count of the number of nodes that have the imperfect string within their body field, using the SELECT query in SQL.

I LOVE SELECT queries because they're totally safe: you can't break anything with SELECT.

SELECT *
FROM `node_revisions`
WHERE `body` LIKE '%<br>%'


In Drupal 7, this might look like:
SELECT *
FROM `field_data_body`
WHERE `body_value` LIKE '%<br>%'


You don't want to use just '%br%', because any number of words, such as 'broken', may contain this string. Be as specific as possible, trying to think of any weird permutations you don't want to change that might use the string you're looking for.

It would also be a good idea to export the nid (node ids) that you're about to change into a CSV file, so you can go back later and spot check them for quality assurance.

Step 3: Exercise EXTREME caution when using the UPDATE query to replace the old string of text with your new string

Third, CAREfully prepare your SQL UPDATE statement.

UPDATE queries can BREAK your site, so tread lightly.

Place this carefully-thought-out UPDATE query into the SQL tab in your phpMyAdmin interface. Be sure to escape reserved characters (such as double quotes) with a slash (\).

Your UPDATE query might look something like this:
UPDATE `node_revisions`
SET `body` = replace(`body`,"<br>","<br/>");


In Drupal 7, this might look like:
UPDATE `field_data_body`
SET `body_value` = replace(`body_value`,"<br>","<br/>");


Step 4: Confirm success

Finally, run the SELECT query from step 2 again, to make sure your UPDATE query worked and spot check a few of the nodes you found in step 2.

SELECT *
FROM `node_revisions`
WHERE `body` LIKE '%<br>%'