oracle - is it possible to regexp_replace using a function? -
i calculations on value in string , replace them. oracles regexp seemes \1 gets evaluated @ end of calcualtions. wondering if fore evaluation before passing function?
set serveroutput on declare l_foo varchar2(4000); function f_test(i_t varchar2) return varchar2 begin dbms_output.put_line('given parameter: ' || i_t); return upper(i_t); end; begin l_foo := regexp_replace( 'http://www.scoach.com/${asset_type}/${isin}?eventtarget=${target}andeventvalue=${target_value}' ,'\$\{([[:alpha:]_]+)\}' ,f_test('\1') ); dbms_output.put_line(l_foo); end; gives result like:
given parameter: \1 http://www.scoach.com/asset_type/isin?eventtarget=targetandeventvalue=target_value pl/sql procedure completed.
it looks passing backreference function within reg ex function not going work (at least in test , lack of finding out there works (although there link hard call reference)
but can this, it'll slow-by-slow processing ought work. based sample link
set serveroutput on
declare l_foo varchar2(4000); searchstring varchar2(4000) ; searchpattern varchar2(4000) ; /*type matchitem object( position number , matchedpattern varchar2(4000));*/ type matched table of varchar2(100); l_foo2 matched; function f_test(i_t varchar2) return varchar2 begin dbms_output.put_line('given parameter: ' || i_t); return upper(i_t); end f_test; function getnmatch( str in varchar2, pat in varchar2, occr in number , flags in varchar2 := null ) return varchar2 pos_match_begin number; pos_match_end number; str_used varchar2(4000); begin pos_match_begin := regexp_instr ( str, -- pat, 1, -- start position occr, -- occurance 0, -- return option flags ); pos_match_end := regexp_instr ( str, -- pat, 1, -- start position occr, -- occurance 1, -- return option flags ); if (pos_match_begin >= 0 , pos_match_end > 0) str_used := substr(str, pos_match_begin, pos_match_end - pos_match_begin); else str_used := null; end if; return str_used ; end getnmatch; function match ( str in varchar2, pat in varchar2, flags in varchar2 := null) return matched ret matched; number ; regcount number ; begin regcount := regexp_count(str, pat) ; ret := matched(); in 1 .. regcount loop ret.extend() ; ret(i) := getnmatch(str, pat , i, flags); end loop; return ret; end match; function rematch ( str in varchar2, pat in varchar2, flags in varchar2 := null) return varchar2 ret matched; str_out varchar2(4000); begin str_out := str; ret := match(str,pat,flags); in reverse 1..ret.count loop str_out := regexp_replace(str_out, pat, f_test(ret(i)),1, i); end loop; return str_out ;--ret; end rematch; begin searchstring := 'http://www.scoach.com/${asset_type}/${isin}?eventtarget=${target}andeventvalue=${target_value}'; searchpattern:= '\$\{([[:alpha:]_]+)\}'; l_foo := rematch( searchstring, searchpattern); --this example call custom function auto-change entire string defined dbms_output.put_line(l_foo); --here example 'allow' use count of table's position pseudo backreference pull out items , scrub them desired l_foo2 := match(searchstring ,searchpattern); dbms_output.put_line('\4/\3,\2: \1 || ' || f_test(l_foo2(4)) || '/' || l_foo2(3) || ',' || l_foo2(2) || ': ' || l_foo2(1)); end; which results in
given parameter: ${target_value} given parameter: ${target} given parameter: ${isin} given parameter: ${asset_type} http://www.scoach.com/${asset_type}/${isin}?eventtarget=${target}andeventvalue=${target_value} given parameter: ${target_value} \4/\3,\2: \1 || ${target_value}/${target},${isin}: ${asset_type} this done in 11gr1. can see looping through , placing results varchar2 table , doing replacement line-by-line there function results. (please note there more efficient ways of doing this, not striving efficiency rather work)
Comments
Post a Comment