将反向引用传递给 Oracle Regexp 中的另一个函数 [英] Passing Backreference to Another Function in Oracle Regexp

问题描述

我正在尝试运行一个普通的 regexp_replace，除了使用反向引用值动态选择替换字符串.

为了提供更多的上下文，我有(在同一个表的两列中，为了简化事情)两个字符串.第一个包含占位符，如 {1}、{2} 作为字符串文字本身的一部分；需要用第二个字符串的相应子字段替换(在用一些常量分隔符标记它之后，比如|").

所以，如果我们有:

Str1 = '敏捷的棕色{1}跳过懒惰的{2}.'

Str2 = '狐狸|狗'

我们需要结果...我相信你明白了.

在理想情况下，Oracle(11g Enterprise 64 位)会让我这样做:

<预><代码>with x as(选择'The quick brown {1} jumps over the lazy {2}.' col1, 'fox|dog' col2 from dual)选择 regexp_replace(x.col1, '({[0-9]+})', regexp_substr(x.col2,'[^|]+',1,'\1')) 作为结果从 x

但是，第二个 regexp_substr 无法识别来自外部 regexp_replace 调用的反向引用并抛出 ORA-17222:invalid number.

我不想创建存储过程或函数，因为我的业务案例是创建一个(可能是物化的)视图来包含这些数据.我更希望有一个单线可以做到这一点.

查看各种论坛，Oracle 可能不支持这种反向引用传递，因此问题标题可能有点误导 - 如果您能指出我的另一种替代方案(不求助于其他 DDL)也一样.我有编程经验，但对 Oracle 本身没有经验，所以请保持温和！

一些浏览表明，人们倾向于使用 MODEL 子句和 xmltables(今天之前没有听说过)等晦涩的东西来解决复杂的正则表达式难题，但我认为可能有一些非常简单的东西我遗漏了.

解决方案

我会使用简单的 PL/SQL 函数 - 这只是一个循环来迭代占位符并替换变量.并且您也可以在 SQL 中使用它来定义视图.

如果你真的想要纯 SQL 解决方案，这里有一个使用公用表表达式的解决方案(它绝对不是笨拙的简单):

 数据为 (选择 1 id，'快速棕色 {1} 跳过懒惰 {2} 和 {3}.txt, '狐狸|狗|猫' ||'|'来自双重变量联合所有选择 2 id, 'Hello {1}' txt, 'world' ||'|'来自双重变量),递归 (id, txt, vars, i) 为 (选择 id, txt, vars, 1 i从数据联合所有选择身份证，替换 (txt,'{' ||我||'}',regexp_substr(vars, '[^|]+')),substr(vars, instr(vars, '|') + 1),我+1从递归其中 instr(vars, '|') >0)选择txtfrom (select txt, row_number() over (partition by id order by i desc) rn从递归)其中 rn = 1;

I am trying to run a vanilla regexp_replace, except the replacement string is dynamically selected using the backreference value.

To give a little more context, I have (say in two columns of the same table, for simplifying things) two strings. The first one contains placeholders like {1}, {2} as part of the string literal itself; which need to be replaced by the corresponding subfields of the second string (after tokenizing it with some constant delimiter, say '|').

So, if we have:

Str1 = 'The quick brown {1} jumps over the lazy {2}.'

Str2 = 'fox|dog'

We need the result as... I'm sure you get the point.

In an ideal world, Oracle (11g Enterprise 64-bit) would let me do this:

with x as (select 'The quick brown {1} jumps over the lazy {2}.' col1, 'fox|dog' col2 from dual)
select regexp_replace(x.col1, '({[0-9]+})', regexp_substr(x.col2,'[^|]+',1,'\1')) as result
from x

However, the second regexp_substr does not recognize the backreference from the outer regexp_replace call and throws an ORA-17222:invalid number.

I don't want to create a stored procedure or a function, as my business case is to create a (possibly materialized) view which will contain this data. I'd much rather there'd be a one-liner that could do this.

Looking at various forums, Oracle may not support this backreference passing, so the question title may be a bit misleading - if you could point me to towards another alternative (without resorting to other DDLs) that'd be just as well. I'm experienced in programming but not in Oracle itself, so please be gentle!

Some browsing has shown people tend to use obscure things like MODEL clauses and xmltables (haven't heard of them before today) for complex regexp puzzles, but I think there may be something awkwardly simple that I'm missing.

解决方案

I would go with simple PL/SQL function - that's just one loop to iterate over placeholders and substitute the variables. And you can also use it inside SQL to define the view.

If you really want pure SQL solution, here is one using common table expressions (it's definitely not awkwardly simple):

with data as (
  select 1 id, 'The quick brown {1} jumps over the lazy {2} and {3}.' txt, 'fox|dog|cat' || '|' vars from dual
  union all
  select 2 id, 'Hello {1}' txt, 'world' || '|' vars from dual
),

recursive (id, txt, vars, i) as (
  select id, txt, vars, 1 i
  from data
  union all
  select id,
         replace (txt,
                  '{' || i || '}',
                  regexp_substr(vars, '[^|]+')),
         substr(vars, instr(vars, '|') + 1),
         i+1
  from recursive
  where instr(vars, '|') > 0)

select txt
from (select txt, row_number() over (partition by id order by i desc) rn
      from recursive)
where rn = 1;

这篇关于将反向引用传递给 Oracle Regexp 中的另一个函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！