[mysql] mass rename of avatar field value

[127.0.0.1]

I was never good with sql to begin with, and now I havent used it for so long I've forgot.

In yabbse there were many values in the members avatar field that were incomplete urls such as "forum/avatars/mypic.gif." This worked fine on yabbse but not so on smf.

Can someone please write me a small query to search all values in the avatar field begining with "forum/avatars/" and append "http://www.whatever.com/" to the value.

Something I could just copy and paste into the phpmyadmin sql query form would be great.

[[Unknown]]

UPDATE smf_members
SET avatar = CONCAT('http://www.whatever.com/';, avatar)
WHERE avatar LIKE 'forum/avatars/%';

Should do it...

-[Unknown]

[127.0.0.1]

Sweet. It worked.

In the future, instead of appending, how would i go about replacing say "forum/avatars"entirely with other text? I think I'd use the replace function but i'm not sure how.

[[Unknown]]

UPDATE smf_members
SET avatar = REPLACE(avatar, 'forum/myavatars/', 'http://www.whatever.com/uberavatars/')
WHERE avatar LIKE 'forum/avatars/%';

-[Unknown]

[127.0.0.1]

thanks. you rule.