搜索&在 MySql 中替换，使用正则表达式 [英] Search & replace in MySql, using regex

问题描述

我有一排内容看起来像这样:

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

我需要在行中搜索所有出现的 [ATTACH]number[/ATTACH] 并像这样替换它:

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

[ATTACH]123456[/ATTACH] 应该变成 [sharedmedia=core:attachments:123456]

或...

[ATTACH]78912[/ATTACH] 应该变成 [sharedmedia=core:attachments:78912]

推荐答案

SQL 未针对此类工作进行优化.以下可能会完成您想要的:

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]';

这假设所有出现的 [ATTACH] 后跟一个数字.

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

这篇关于搜索&在 MySql 中替换，使用正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！