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

Question

Suppose I have the following MySQL data

userfiles/user/JohnSmith/2013_Thanksgiving_Final.jpg
userfiles/user/JaneJohnson/2013_Thanksgiving_Final.jpg
userfiles/user/BobbyLee/2013_Thanksgiving_Final.jpg

Is there a way to change it to?

globalfiles/2016_Thanksgiving_Final.jpg
globalfiles/2016_Thanksgiving_Final.jpg
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!

you want remove the part 'yserFiles/user/JohnSmith/ and repalce with globlfiles for all the user where there is 2013_Thanksgiving_Final.jpg? — ScaisEdge
– ScaisEdge, Commented Oct 27, 2016 at 15:50

ScaisEdge · Accepted Answer · 2016-10-27 15:48:30Z

1

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

answered Oct 27, 2016 at 15:48

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

myaddresstoday Over a year ago

Ah I was going about it all wrong, thank you for the help!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related