SQL: Find and Replace with SQL?

I have a MySQL InnoDB database.

I have a column my in 'article' table called url that needs to 开发者_Python百科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?

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

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

I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:

REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), 
       “something else we are looking for”, “replace second instance”)

This is the syntax I used to detect a boolean value:

REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)

Hope this helps!