MySQL find and replace all instances of a string

I recently had to move a video player into a different folder location that was referenced multiple times in a company Intranet. To prevent the video player from breaking this required a mass find and replace on the database, though I didn't know where in the database this information was stored. In this post I'm going to look at how to find which tables in a MySQL database a string is referenced, and how to to replace all instances of that string using MySQL's replace function.  

Locate the string that you want to replace

As I mentioned in the opening paragraph, you might not always know which tables in a database a certain string is going to be referenced. This can easily be found by taking the following steps:

  • Log on to phpMyAdmin
  • Click on 'Search' and enter the string with %wildcards% appending and prepending your search term so that it will seek the string with any other characters before or after it. In the example below I'm looking for a match against a url that references a 'media' folder:

 %www.mysite.com/media/%

  • Select all or any of the tables you think are relevant to your search and click to search. This will provide you with a list of matches against each of the tables, which should help you identify the location of the string. For this example let's say the instances of this string were in your 'posts' table in the 'description' column.

MySQL Replace

Now we know where the data is we're going to use MySQL's 'Replace' function:

REPLACE(text_string, from_string, to_string)

The MySQL documentation describes this as:

"Returns the string str with all occurrences of the string from_str replaced by the string to_str.
REPLACE() performs a case-sensitive match when searching for from_str."

So as an example if we wanted to remove the 'www.' from a string we could do the following:

SELECT REPLACE('www.mysite.com', 'www.', '');

Implementing a string replace with a table update

To implement this on a table update we would pass the column name into the first parameter of the replace function like so:

UPDATE posts SET description = REPLACE(description,
'currentstring','replacementstring');

 

Replace all instances of a string in a WordPress database

A default WordPress installation only comes with 11 tables, of which there's only a handful of columns that contain content you might want to do a string replacement on. The following SQL statements covers all the main tables and content columns for a site wide update (posts, pages, comments, links, taxonomy and user details). Simply replace 'xcurrentx' and 'xreplacementx' with strings. Keep in mind this does not include any additional tables that may be created by other plugins you've installed.

UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_comments SET comment_content = REPLACE(comment_content,'xcurrentx','xreplacementx');
UPDATE wp_links SET link_description = REPLACE(link_description,'xcurrentx','xreplacementx');
UPDATE wp_options SET option_value = REPLACE(option_value,'xcurrentx','xreplacementx');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_content = REPLACE(post_content,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_title = REPLACE(post_title,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt,'xcurrentx','xreplacementx');
UPDATE wp_term_taxonomy SET description = REPLACE(description,'xcurrentx','xreplacementx');
UPDATE wp_usermeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');


I'd strongly recommend backing up your database before using the replace command incase you get any unexpected results!

 

Sign Up
comments powered by Disqus
Sign Up

Popular Tags

350x250

Need a web developer?

If you'd like to work with code synthesis on your next project get in touch via the contact page.