MySQL: Replace A String In A Column

I recently encountered a situation where I needed to replace part of a string value for all of the records in a particular column. This is actually not the first time that I have encountered this scenario, so I thought that this time I would document it.

By the way , this solution was actually submitted by Dmytro Shevchenko on Stack Overflow. So if this was useful, head over there and give his answer an upvote!

Lets say I had a column called urls that contained - yes, you guessed it - URLS.

The way to replace them is by using the REPLACE function in MySQL:

UPDATE urls
SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/');

Thats it. REPLACE takes three parameters:

  • The column name of what data you want to replace
  • The string that you wish to replace
  • The replacement string

Note that this would also work if the string to replace was just a substring of a larger string.

We can go one step further to improve the query. We can select only the records that contain that URL in the the column with a simple WHERE clause.

UPDATE urls
SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/')
WHERE url LIKE %domain1.com/images/%;

I have done a bit of testing and actually found that using the WHERE statement can actually slow it down by a fraction of a second but I guess it can depend on the rest of your statement and your specific use case (in my test, it updated 5 records out of 122 and there was a time difference of approximately 0.001 seconds).


© 2012-2017