MySQL for replace with wildcard

Question

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

So that

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

becomes

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

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

Answer 1

Update: MySQL 8.0 has a function REGEX_REPLACE().

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

---

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.