MySQL

MySQL equivalent of str_replace | Search and Replace

A great little time saving tip when doing database management, or any sort of migrations within domains that might require you to modify a certain part of a string in a MySQL column.

The issue:

You have an online store that stores all the products in a MySQL table called ‘products’, your images are hard coded links to something similar to http://www.mydomain.com/image/example_image.jpg but you have now moved to ‘newdomain.com’ so you need all your images to look like: http://www.newdomain.com/image/example_image.jpg.

Rather than having to create a PHP script for this, you can use the rather “cool” SQL statement:

UPDATE `table` SET `column`=replace(`column`,`old_text`, `new_text`);

So for our example above we would carry out:

UPDATE `table` SET `descriptions`=replace(`descriptions`,`mydomain.com`, `newdomain.com`);

NOTE: The code above will change ALL mentions of ‘mydomain.com’ to ‘newdomain.com’, you might require it to be more precise in which case you just need to fine tune the “old_text” and the “new_text” sections to look and change what you require.

Tags: ask vince, MySQL, replace, str_replace, update