myaddresstoday myaddresstoday - 1 month ago 6
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.

Thanks!

Answer

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%';
Comments