Chris Chris - 2 months ago 7
MySQL Question

Changing forum content tags in string column

In the process on migarting from one forum software to another I have been left with mis-matched embedded BBCodes in hundreds of posts.

For example this is the content of one post:

Luke Kelly - Raglan Road + Lyrics
[video]https://youtu.be/8xvkvFviIj8[/video]


My new forum expects:

Luke Kelly - Raglan Road + Lyrics
[MEDIA]https://youtu.be/8xvkvFviIj8[/MEDIA]


It's beyond my SQL abilities to substitute the tags.

The table name is
xf_post
and the field name is
message


Any help would be appreciated, thanks.

Answer

Simple UPDATE command with nested replace(string, string, string) would work:

UPDATE xf_post
SET 
  message = replace(replace(message, '[video]', '[MEDIA]'),'[/video]','[/MEDIA]'));
Comments