如何在Oracle中转义regexp_replace? [英] How to escape a regexp_replace in Oracle?

问题描述

我正在为字符串创建一个小的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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！