[HowTo] Replace String In Mysql With One Query

Yesterday i was helping my friend’s blog. She want to change her domain and move her hosting server for some reasons. She is using wordpress as her blog engine and of course using mysql as database server. To move wordpress blog to another domain you need to change some configuration on the database file. Especially if the data have the absolute path in url (ex: http://www.willbechange.com/bla/bla/post.html).

After examine her database file i found that many of data in wp-options using absolute path for the url. And this data need to be changed if you moved your domain. You can replace that data in table just with one simple query in mysql.

For example, the old domain is www.olddomain.com and the new domain is www.newdomain.com. In MySQL you can use the String function to replace string called REPLACE(). This can be done by 1 simple query, here is the format:

UPDATE `table_name` set `field_name` = REPLACE(`field_name`, 'Keyword to be replaced', 'Replacement String');

For example, in my case i want to update wp-options then here is the query:

UPDATE `wp-options` set `option_values` = REPLACE(`option_values`, 'www.olddomain.com', 'www.newdomain.com');

Run the query and all data in wp-options will be update to new domain.

It’s not hard, isn’t it? Thanks for reading my blog. Please leave any comment if you have other idea or tips to share or even a question.