大家好,又见面了,我是你们的朋友全栈君。
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.
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/160508.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...