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).