MySQL 用通配符替换

2021-12-25 00:00:00 sql replace mysql

我正在尝试编写 SQL 更新以用新字符串替换特定的 xml 节点:

I'm trying to write a SQL update to replace a specific xml node with a new string:

UPDATE table
SET Configuration = REPLACE(Configuration,
     "<tag>%%ANY_VALUE%%</tag>"
     "<tag>NEW_DATA</tag>");

这样

SDADAS

变成

NEW_DATA

这种类型的请求是否缺少语法?

Is there a syntax im missing for this type of request?

推荐答案

更新:MySQL 8.0 有一个功能 REGEX_REPLACE().

Update: MySQL 8.0 has a function REGEX_REPLACE().

以下是我 2014 年的回答，它仍然适用于 8.0 之前的任何版本的 MySQL:

Below is my answer from 2014, which still applies to any version of MySQL before 8.0:

REPLACE() 不支持通配符、模式、正则表达式等.REPLACE() 仅将一个常量字符串替换为另一个常量字符串.

REPLACE() does not have any support for wildcards, patterns, regular expressions, etc. REPLACE() only replaces one constant string for another constant string.

你可以尝试一些复杂的事情，挑出字符串的前导部分和字符串的尾随部分:

You could try something complex, to pick out the leading part of the string and the trailing part of the string:

UPDATE table
SET Configuration = CONCAT(
      SUBSTR(Configuration, 1, LOCATE('<tag>', Configuration)+4),
      NEW_DATA,
      SUBSTR(Configuration, LOCATE('</tag>', Configuration)
    )

但这不适用于多次出现 的情况.

But this doesn't work for cases when you have multiple occurrences of <tag>.

您可能需要将行提取回应用程序，使用您喜欢的语言执行字符串替换，然后将行回传.换句话说，每行的三步过程.

You may have to fetch the row back into an application, perform string replacement using your favorite language, and post the row back. In other words, a three-step process for each row.

相关文章