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]

Thank you for providing a report on the status of your current efforts. Did you have a question? — spencer7593
– spencer7593, Commented Mar 15, 2014 at 16:13

Gordon Linoff · Accepted Answer · 2014-03-15 16:16:33Z

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.

edited Mar 15, 2014 at 16:16

answered Mar 15, 2014 at 16:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@Akyhne . . . That is why I always try to line up my parentheses, so I can visually see when I might be missing one.

Akyhne Over a year ago

Works this way: 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.

Collectives™ on Stack Overflow

Search & replace in MySql, using regex

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related