I inherited an antiquated Drupal 6 site from a client and was contracted to upgrade it to Drupal 8. There really is no direct upgrade path to follow here, but the trials and tribulations involved aren’t relevant to this post. What is: they made a request to change the name of their company throughout the entire site. At this point it had grown into quite a large site with dozens of users adding and updating content on a regular basis.
The challenge of this search and replace task came with the fact that their name was cased in a very specific way. I won’t mention their name, so let’s just say it’s Company. However, they referred to it as “cOmpany” on most pages, and just “Company” on others. All of these had to be “COMPANY” now.
Enter regular expressions and binary.
Suppose we want to find all mentions of “cOmpany“:
SELECT node__body FROM node__body WHERE BINARY body_value REGEXP '[[:<:]]c[[:upper:]]mpany[[:>:]]'This does the trick, and gives us an idea of how many there are. Then we take our very handy UPDATE / REPLACE SQL:
UPDATE [table_name] SET [field_name] = REPLACE([field_name],'[string_to_find]','[string_to_replace]');and modify it to take case sensitivity into account:
UPDATE node__body SET body_value = REGEXP_REPLACE(body_value, 'cOmpany', 'COMPANY') WHERE BINARY body_value REGEXP '[[:<:]]c[[:upper:]]mpany[[:>:]]