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?
update url
set article = replace(article, '/blog/', '/news/')
where article like '/blog/%'
/blog/ in the middle of an article url.
where clause as you typed that.
set article = '/news/' + substring(article, 7) instead, maybe also where article like '/blog/%' and len(article) >= 7 just to be safe.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!
