sql - postgres SQL 更新列，其值来自正则表达式 + 更多

Question

我想更新 db.table.column 中所有匹配的 url。我想在 url 的末尾添加一个参数，并将其他所有内容保持原样。

我试过这个：

UPDATE table SET column = regexp_replace ( column , 'url_matcher', ( substring ( column , 'url_matcher') || 'end_param');

UPDATE
    "db"."emailTemplate"
set 
    "bodyText" = regexp_replace(
        "bodyText",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyText" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    ),
    "bodyHtml" = regexp_replace(
        "bodyHtml",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyHtml" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    );

但子字符串与整个 url 不匹配，它只匹配 {{app.url}} 或 {{@root.app.url}} url 的其他所有内容都不匹配。

当我单独运行 regexp_replace("bodyHtml", [same pattern], 'replace_thing') 时，它匹配整个 url 并用“replace_thing”替换它。因此，在一种情况下，正则表达式模式有效，而在另一种情况下则无效。所有与 url 不匹配的字段都不会更新，但在使用 substring 函数时也会更新，如我的示例所示。所有与 url 不匹配的字段都更新为 null。

1. 我不能使用这样的嵌套函数吗？如果不是 - 我怎样才能实现我想要的？

2. 其他正则表达式模式规则是否适用于 regexp_replace 之外的子字符串函数？

列值的最大值：Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page regexp_replace 将匹配：{{app.url}}/home:search=true&item=33，子字符串将仅匹配{{app.url}}

更新后列值应如下所示：

Hello user, click here {{app.url}}/home:search=true&item=33&param={{param}} to visit your page

但它最终看起来像这样：

Hello user, click here {{app.url}}&param={{param}} to visit your page

像这样的字段：Hello you have been registered!看起来像这样：null但应该保持不变。

标签： sqlregexpostgresqlsubstringregexp-replace

Answer 1

正则表达式模式用于搜索匹配的文本。在替换模式中，您不能使用正则表达式，只能使用特定构造，例如替换反向引用（如 \1）来引用捕获组 1 的值。另外，替换时需要特殊的回调来修改匹配值，这里不支持。

(您可以用and包装整个模式)（以创建捕获组）并使用如下正则表达式：

'({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})'

替换看起来像

'\1&param={{param}}'

其中\1是对第 1 组值的替换反向引用。查看在线演示

select regexp_replace('Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page',
                      '({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})',
                      '\1&param={{param}}');