使用通配符替换的MySQL [英] MySQL for replace with wildcard

问题描述

我正在尝试编写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>");

如此

<root><tag>SDADAS</tag></root>

成为

<root><tag>NEW_DATA</tag></root>

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

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

推荐答案

更新:MySQL 8.0具有功能

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)
    )

但这不适用于当您多次出现<tag>的情况.

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.

这篇关于使用通配符替换的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！