How can I change an URL inside a field in MySQL?

Question

I have a table called "wp-posts" with a field "post-content". This field contains the text for a blog posts. I'd like to change all records to replace an URL for another one.

Imagine that I can have things like:

This is a test and somewhere there's something like <img src="http://oldurl.com/wp-content/somimg.jpg"&gt; and something like <a href="http://oldurl.com/something"&gt;a link</a>."

I want it to be

This is a test and somewhere there's something like <img src="http://newurl.com/wp-content/somimg.jpg"&gt; and something like <a href="http://newurl.com/something"&gt;a link</a>."

I need to be able to change this for every record in my table without having to open each post in Wordpress and change them by hand. There has to be a way to do this

Answer 1

Use the replace string function in MySQL:

UPDATE MyTable SET textfield = REPLACE(textfield, "http://oldurl.com/", "http://newurl.com")

Answer 2

This can be easily achieved with a simple SQL statement using MySQL's replace() function. Before we do that, you should definitely do a database dump or whatever you use for backups. It's not only that it's The Right Thing To Do™, but if you make a mistake on your substitution, it might prove difficult to undo it (yes, you could rollback, but you might only figure out your mistake later on.)

To create a database dump from MySQL, you can run something like this --

mysqldump -h hostname -u username -p databasename > my_sql_dump.sql

Where (and you probably know this, but for the sake of completeness for future generations...) --

• hostname is a placeholder for the database hostname. If the database server is running on your own machine, then you can either use "localhost" or simply leave the "-h hostname" off entirely
• username is a placeholder for the user with permission to run a dump on the database. This is often an admin, but if it's a shared db, it might simply be you.
• databasename is the name of the whole database containing your tables. (Note that the "-p" has nothing to do with this database name. "-p" indicates that MySQL should ask you for a password in order to log in.)

Now that we got that out of the way, you can log in to the MySQL database using:

mysql -h hostname -u username -p databasename

And simply run this statement:

UPDATE `wp-posts` SET `post-content` = REPLACE(`post-content`, "http://oldurl.com", "http://newurl.com");

And that should do it!

If you make a mistake, you can often rerun the statement with the original and new texts inverted (if the new text -- in your case the new URL -- didn't already exist in the text before you did the replace.) Sometimes this is not possible depending on what the new text was (again, not likely in your case.) Anyway, you can always try recovering the sql dump --

cat my_sql_dump.sql | mysql -h hostname -u username -p databasename

And voilà.

Answer 3

There's a handy Wordpress plugin that I've used to search and replace in posts pages with grep:

http://urbangiraffe.com/plugins/search-regex/

Answer 4

I recently used http://wordpress.org/extend/plugins/search-and-replace/ to update a site from a development server to the public server. I used the plugin to change all URLs of images from their "00.00.00.00/~user/" to their "example.com" format.

It worked perfectly.

Of course, be sure to backup the database first in case you happen to make a typo during the process.