SQL update: doing a find/replace on part of a field

SQL is one of those things I rarely use, and it’s normally the simple stuff, but doing anything a little more complex can be non-obvious. For example, I had a bunch of URLs where I just need to update a part of the URL, as we were moving some files on a website. I eventually found what I wanted within the comments to the MySQL UPDATE command documentation. (credit to Alex de Landgraaf on July 21 2004):

UPDATE xoops_bb_posts_text
SET post_text=(
REPLACE (post_text,
‘morphix.sourceforge.net’,
‘www.morphix.org’));

UPDATE table
SET fieldname=(
REPLACE (fieldname,
‘/somedir/images/’,
‘/newhome/graphics/’));
Does the trick nicely on MySQL :)

Posted in code by chriskeene