Search & replace in MySql, using regex

Question

I have a row with content that can look like this:

bla bla bla [ATTACH]123456[/ATTACH] bla bla bla
bla bla bla [ATTACH]78912[/ATTACH] bla something bla

I need to search the row for all occurences of [ATTACH]number[/ATTACH] and replace it like this:

[ATTACH]123456[/ATTACH] should become [sharedmedia=core:attachments:123456]

or...

[ATTACH]78912[/ATTACH] should become [sharedmedia=core:attachments:78912]

Answer 1

SQL is not optimized for this sort of work. The following might accomplish what you want:

update table t
    set content = replace(replace(content, '[ATTACH]', '[sharedmedia=core:attachments:'
                                 ), '[/ATTACH]', ']'
                         )
    where content like '[ATTACH]%[/ATTACH]';

This assumes that all occurrence of [ATTACH] are followed by a number.