myaddresstoday myaddresstoday - 9 months ago 33
MySQL Question

MySQL replace a whole string based off part of the string?

Suppose I have the following MySQL data

  1. userfiles/user/JohnSmith/2013_Thanksgiving_Final.jpg

  2. userfiles/user/JaneJohnson/2013_Thanksgiving_Final.jpg

  3. userfiles/user/BobbyLee/2013_Thanksgiving_Final.jpg

Is there a way to change it to?

  1. globalfiles/2016_Thanksgiving_Final.jpg

  2. globalfiles/2016_Thanksgiving_Final.jpg

  3. globalfiles/2016_Thanksgiving_Final.jpg

I was thinking doing something like this?

UPDATE user__attributes SET value = REPLACE(value, REGEXP '2013_Thanksgiving_Final', 'globalfiles/2016_Thanksgiving_Final.jpg') WHERE value LIKE '%2013_Thanksgiving_Final%';

I was thinking if I used REGEXP, it would target the whole string, based off just a piece of it to replace? Though I threw an error attempting this.


Answer Source

If you want add globalfiles at existing files name the You don't need regexp but simply replace

UPDATE user__attributes 
SET value = REPLACE(value, '2013_Thanksgiving_Final.jpg', 'globalfiles/2016_Thanksgiving_Final.jpg' ) 
WHERE value LIKE '%2013_Thanksgiving_Final%';

if you want substitute the userfiles/user/*/2013_Thanksgiving_Final.jpg with globalfiles/2016_Thanksgiving_Final.jpg you should use

UPDATE user__attributes 
SET value = globalfiles/2016_Thanksgiving_Final.jpg
WHERE value LIKE '%2013_Thanksgiving_Final%';