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.

Deep-B
  • 1,344
  • 1
  • 13
  • 22

2 Answers2

2

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;
Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60
  • Cool, though I can't claim to understand it... But I'll try it out tomorrow at the office. I'd have no qualms about creating a function if I could create a temporary one at runtime inside the CREATE VIEW DDL. Something like a lambda expression I suppose... Does pl/SQL support that? – Deep-B Oct 23 '13 at 00:49
  • No, PL/SQL does not support lambdas. Any reason why you don't want to use compiled function? /Note: I updated the query above to be able to operate on a table rather than single line of input/ – Kombajn zbożowy Oct 23 '13 at 07:10
  • My business case was just for creating a view and I pretty much said this should be simple considering Oracle supports regexps... creating other db objects would necessitate another round of checks through several levels of reddish tape, I'm afraid. While not impossible I'd much rather avoid it, unless not doing so makes this take a significant performance hit. – Deep-B Oct 23 '13 at 09:48
  • I modified the SQL to meet my case and it worked (I used the rowid of the underlying table as the id)... I understand what you're doing in the query now - this is pretty nifty! I didn't know you could do a recursive select in PL/SQL. – Deep-B Oct 23 '13 at 12:54
0

Actually, you should try this (my previous answer forgot to capture in regexp_substr):

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,'i',1)) as result from x;
Philip Allgaier
  • 3,505
  • 2
  • 26
  • 53
Huilian
  • 1