Search code examples
mysqlregexstringphpbb

Replacing a formatted string in MySql


I'm trying to replace all instances of an old BB tag markup in a MySql database with a newer, slightly different one.

The old format is this...

[youtube:********]{Video ID}[/youtube:********]

Which I would like to replace with this...

[youtube:********]http://www.youtube.com/watch?v={Video ID}[/youtube:********]

Where the *'s are a random string of alpha-numeric characters. So simply REPLACE(feild, '[youtube:********]', '[youtube:********]http://www.youtube.com?watch?v= won't do unfortunately.

All the clumsy attempts I've made using REPLACE() and INSTR() have resulted in nasty things like [b]Bold Text[/b]http://www.youtube.com/watch?v=

Is there a way to do this kind of pattern replacement in MySql? Possibly with Regular Expressions?

Thank you.


Solution

  • Is this what you tried?

    UPDATE table SET Field = REPLACE(Field,']{',']http://www.youtube.com/watch?v={')
    

    This would depend if there isnt any other occurences of ']{'

    EDIT: You may also want to try:

    UPDATE table SET Field = LEFT(Field,#) + 'http://www.youtube.com/watch?v='+ 
    RIGHT(Field,(Char_Length(Field)-#);
    

    Just check the syntax with MYSQl docs. Char_LNEGTH() may need to be LENGTH() - im sure you get the idea