While messing around with a PHP script for string replacement (using the str_replace function) in a MySQL database field, I came across a way to find and replace the string by using only MySQL. Maybe it’s just my ignorance of MySQL’s capabilities, but I didn’t think there would be anyway to do this without the use of PHP or any other scripting language. In this case I was trying to remove a domain name from an entire column for the purpose of converting absolute URLs to relative URLs. Anyway, I thought I would post it here for future reference.
If you run the following query:
UPDATE table_name SET column_name = replace(column_name,’http://www.example.com’, ”)WHERE column_name LIKE ‘%http://www.example.com%’;
It will take URLs like this:
http://www.example.com/directory/page1.html
http://www.example.com/directory/page2.html
http://www.example.com/directory/page3.html
And change them to:
/directory/page1.html
/directory/page2.html
/directory/page3.html
Of course it can be done with any kind of string that appears in many fields, not just URLs.
