MySQL String Replace

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.

Comments are closed.

Recent Cycling

  • 31.90 miles on 09/15/17
  • 9.30 miles on 09/09/17
  • 8.70 miles on 09/08/17
  • 8.60 miles on 09/04/17
This Month:

0.00 miles

Last Month:

0.00 miles

This Year:

354.90 miles

Last Year:

604.10 miles