regex - Oracle: Is There a Variant of REGEX_REPLACE that supports inline code? -

note: question has been asked in context of c++ regexp_replace (see here). however, occurred me might of interest in oracle universe, too. wording of question has been adopted kind permission of original author.

perl has e regex modifier allows perl code rather string formulate replacement: http://perldoc.perl.org/perlretut.html#search-and-replace though example not greatest there switches accomplish this. of understand perl here's example makes more sense:

$string = "stackoverflow user: old faithful";  $string =~ s/:\s*(.*)$/$1 == "old faithful" ? ": ".$1." awesome!" : ": ".$1." ???"/e;  print $string; #will print "stackoverflow user: old faithful awesome!" 

is there regex_replace variant in (pl)sql allow me similar? in code inline replacement.

unfortunately, regex_replace doesn't cater dynamically generated replacement strings.

however, can come close. answer contains 3 variations of same idea. in nutshell:

• variation 1: static
  simplest, computations in expression syntax only, sql compatible, performance nightmare

• variation 2: static + function call

  simple, complex computations, sql compatible, performance nightmare;
  privileges create function/packages needed.

• variation 3: dynamic
  complex, utmost flexibility in executed code, not sql compatible, performance ... guessed it.

the basic idea of methods use regexp_substr hold of capture group contents , feed these contents code computing data substituted it. in variation 1 code expression itself, variation 2 hides code in function body , variation 3 implements dynamic plsql block around it.

variant 1 (static)

use regexp_substr hold of capture group contents , feed expression process data accordingly:

set serveroutput on declare     text_orig_yep varchar2(1000) := 'stackoverflow user: old faithful';     text_orig_nay varchar2(1000) := 'stackoverflow user: nobody';     text_pattern  varchar2(1000) := ':\s*(.*)$';     text_repl     varchar2(1000); begin     text_repl :=        regexp_replace (             text_orig_yep           , text_pattern           , case regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)                when 'old faithful' regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)||' awesome!'                else                     regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)||' ???'              end        );     dbms_output.put_line ( text_repl );     text_repl :=        regexp_replace (             text_orig_nay           , text_pattern           , case regexp_substr(text_orig_nay, text_pattern, 1, 1, '', 1)                when 'old faithful' regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)||' awesome!'                else                     regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)||' ???'              end        );     dbms_output.put_line ( text_repl ); end; / show error 

variant 2 (static + function call)

use regexp_substr hold of capture group contents , feed function compute results. way can perform complex computations impossible or cumbersome express plsql expression.

set serveroutput on create or replace function test_rreval ( match_1 in varchar2 ) return varchar2 begin     return         case match_1             when 'old faithful' match_1||' awesome!'             else                     match_1||' ???'          end     ; end test_rreval;    / show error  declare     text_orig_yep varchar2(1000) := 'stackoverflow user: old faithful';     text_orig_nay varchar2(1000) := 'stackoverflow user: nobody';     text_pattern  varchar2(1000) := ':\s*(.*)$';     text_repl     varchar2(1000); begin     text_repl :=        regexp_replace (             text_orig_yep           , text_pattern           , test_rreval ( regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1) )        );     dbms_output.put_line ( text_repl );     text_repl :=        regexp_replace (             text_orig_nay           , text_pattern           , test_rreval ( regexp_substr(text_orig_nay, text_pattern, 1, 1, '', 1) )        );     dbms_output.put_line ( text_repl ); end; / show error 

variant 3 (dynamic)

you gain utmost flexibility @ cost of performance , maintainability generating dynamic plsql block produce result.as perk can keep closer perl syntax ( can tweak variation 2 accordingly ).

set serveroutput on declare     text_orig_yep varchar2(1000) := 'stackoverflow user: old faithful';     text_orig_nay varchar2(1000) := 'stackoverflow user: nobody';     text_pattern  varchar2(1000) := ':\s*(.*)$';     text_repl     varchar2(1000);     dyncode       varchar2(32000); begin     dyncode := 'declare "$1" varchar2(32000) := :1; begin :outvar := regexp_replace ( :text_orig, :text_pattern, case "$1" when ''old faithful'' "$1"||'' awesome!'' else "$1"||'' ???'' end ); end;';     execute immediate dyncode                  using in   regexp_substr(text_orig_yep, text_pattern, 1, 1, '', 1)                     , out  text_repl                     , in   text_orig_yep                     , in   text_pattern                     ;     dbms_output.put_line ( text_repl );     execute immediate dyncode                  using in   regexp_substr(text_orig_nay, text_pattern, 1, 1, '', 1)                     , out  text_repl                     , in   text_orig_nay                     , in   text_pattern                     ;     dbms_output.put_line ( text_repl ); end; / show error