0

I was trying to replace a mysql string/text field by another. The problem I have is that the text/varchar field contains bbcode with suffix(like [image:sgzarkty] [/image:sgzarkty]), I want to get rid of the ugly suffix so the bbcode looks cleaner(like [image] [/image]). But the problem is, the suffix can be 6-8 characters long, so I cannot just use substring function to strip it. I am thinking that regular expression can help, but how? Heres what I have with substring replacement, can anyone modify it to work with regular expression for any number of characters?

UPDATE
    post
SET
    posttext = replace( posttext, substring( posttext, locate( '[spoiler', posttext ) , 17 ) , '[spoiler' )
WHERE
    LOCATE( '[spoiler', posttext ) >0
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Lord Yggdrasill
  • 3,197
  • 4
  • 26
  • 42
  • 1
    The MySQL `REGEX` operator returns a boolean. You wouldn't be able to use MySQL `REGEX` to do string replacement or otherwise modify a string. (You might be able to make use of it in a larger expression.) – spencer7593 Aug 11 '14 at 13:55
  • 1
    possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – TJ- Aug 11 '14 at 13:56

0 Answers0