SQL: Find and Replace with SQL?

Question

I have a MySQL InnoDB database.

I have a column my in 'article' table called url that needs to be updated.

Stored in article.url =

/blog/2010/article-name
/blog/1998/the-article-name
/blog/...

I need to change /blog/ to /news/. (E.g. now article.url = '/news/...')

What is the SQL needed to replace "/blog/" with "/news/" in the article.url column?

Answer 1

update url
set article = replace(article, '/blog/', '/news/')
where article like '/blog/%'

Answer 2

If every url starts with "/blog/" and you don't want to change anything except the prefix, then you can just use substring() and concat() instead of replace():

update article
set url = concat('/news/',substring(url,7))
where url like '/blog/%';