mysql 通配符 替换,使用通配符替换的MySQL

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

UPDATE table

SET Configuration = REPLACE(Configuration,

"%%ANY_VALUE%%"

"NEW_DATA");

So that

SDADAS

becomes

NEW_DATA

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

解决方案

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('', Configuration)+4),

NEW_DATA,

SUBSTR(Configuration, LOCATE('', Configuration)

)

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

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.