问题描述
我正在为字符串创建一个小的replaceParam函数,并希望能够转义一个替换,例如e. g.
I am creating a small replaceParam function for Strings and wanted to be able to escape a replacement, e. g.
select regexp_replace('%ABC# %ABC#','%ABC#', 'XXX')
from dual;
导致
XXX XXX
但是我希望能够摆脱替换,例如. G.通过在字符串前面加\来代替,否则将被替换.
BUT I want to be able to escape the replacment, e. g. by preceding a \ in front of a string, that would be otherwise replaced.
select regexp_replace('%ABC# \%ABC#','<themagicregexp>', 'XXX')
from dual;
应该导致
XXX \%ABC#
我尝试了不匹配的字符列表,但这不起作用.
I tried the non-matching character list, but this doesn't work.
select regexp_replace('%ABC#abc\%ABC#','<themagicregexp>', 'XXX')
from dual;
应该导致
XXXabc\%ABC#
此外,因为有人提到它:我不能使用单词边界,因为它也可以工作:
In addition because someone mentioned it: I can't go with word boundaries, since this should work also:
yoyo%ABC#yoyo
我感觉这可以在一个正则表达式中完成,但是我只是看不到?
I have the feeling this can be done in one regexp, but I just don't see it?
推荐答案
如果您没有类似%ABC#%ABC#
SELECT REGEXP_REPLACE( '%ABC#abc\%ABC#', '((^|[^\])(\\\\)*)%ABC#', '\1XXX' )
FROM DUAL;
这将匹配:
- 字符串
^
或非斜杠字符[^\]
的开头,后跟任意对的斜杠字符对,最后是字符%ABC#
.这将匹配%ABC#
,\\%ABC#
,\\\\%ABC#
等,但不匹配\%ABC#
,\\\%ABC#
,\\\\\%ABC#
,在其中使用斜杠将%
字符转义.
- The start of the string
^
or a non-slash character[^\]
followed by any number of pairs of slash characters then, finally, the characters%ABC#
. This will match%ABC#
,\\%ABC#
,\\\\%ABC#
and so on but will not match\%ABC#
,\\\%ABC#
,\\\\\%ABC#
where there is a slash escaping the%
character.
替换项包括第一个捕获组,因为表达式可以匹配前面的非斜杠字符和斜杠对,并且这些对必须保留在输出中.
The replacement includes the first capture group as the expression can match a preceding non-slash character and pairs of slashes and these need to be preserved in the output.
更新
这有点复杂,但是会重复进行匹配:
This gets a bit complicated but it will do repeated matches:
WITH Data ( VALUE ) AS (
SELECT '%ABC#%ABC#' FROM DUAL
)
SELECT ( SELECT LISTAGG(
REGEXP_REPLACE( COLUMN_VALUE, '((^|[^\])(\\\\)*)%ABC#$', '\1XXX' ),
NULL
) WITHIN GROUP ( ORDER BY NULL )
FROM TABLE(
CAST(
MULTISET(
SELECT REGEXP_SUBSTR( d.value, '.*?(%ABC#|$)', 1, LEVEL )
FROM DUAL
CONNECT BY LEVEL < REGEXP_COUNT( d.value, '.*?(%ABC#|$)' )
AS SYS.ODCIVARCHAR2LIST
)
)
) AS Value
FROM Data d;
它使用相关的子查询将字符串分成以%ABC#
或字符串结尾(这是TABLE( CAST( MULTISET( ) .. ) )
内的位)结尾的子字符串,然后重新连接这些子字符串,在每个子字符串的末尾执行替换后的字符串.
It uses a correlated sub-query to split the string into sub-strings that end with %ABC#
or the end-of-string (this is the bit inside the TABLE( CAST( MULTISET( ) .. ) )
) and then re-concatenates these sub-strings after performing the replacement on the end of each sub-string.
这篇关于如何在Oracle中转义regexp_replace?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!