是否可以使用函数regexp_replace? [英] is it possible to regexp_replace using a function?

问题描述

我想对字符串中的值进行一些计算，最后替换它们. Oracle的正则表达式看起来不错，但是\ 1在所有计算结束时都会得到评估.因此，我想知道是否可以在将评估传递给函数之前进行评估?

I would like to do some calculations on a value in a string and finally replace them. Oracles regexp seemes to be good but the \1 gets evaluated at the end of all calcualtions. So I am wondering if I could fore the evaluation before passing it to the function?

set serveroutput on

declare
  l_foo varchar2(4000);

  function f_test(i_t varchar2) return varchar2
  is
  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;

为您提供如下结果:

given parameter: \1
http://www.scoach.com/asset_type/ISIN?eventtarget=targetANDeventvalue=target_value
PL/SQL procedure successfully completed.

推荐答案

似乎将后向引用传递到reg ex函数中的函数中是行不通的(至少在我的测试中，并且缺乏在此找到任何东西的能力)可以正常工作(尽管有链接，但很难将其称为参考)

it looks like passing the backreference into a function within the reg ex function is not going to work (at least in my test and the lack of finding anything out there that works properly (although there was this link but it is hard to call this a reference)

但是您可以执行此操作，但是处理速度会很慢，但是应该可以.我基于此链接

But you can do this, but it'll be slow-by-slow processing but it ought to work. I based this sample from this link

将服务器输出设置为

declare
  l_foo varchar2(4000);

  searchString varchar2(4000) ;
  searchPattern varchar2(4000) ;

  /*type matchItem is object(
       position number ,
         matchedPattern varchar2(4000));*/
  type matched is table of varchar2(100);


  l_foo2 matched;

  function f_test(i_t varchar2) return varchar2
  is
  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 is
    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 and pos_match_end > 0) THEN 
            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 is 

    ret matched;

    i number ;
     regCount number ;

      begin
        regCount :=  regexp_count(str, pat) ;
      ret := matched();

       for i 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
      is
      ret matched;
      str_out varchar2(4000);
      begin
      str_out := str;
        ret := match(str,pat,flags);

         for i 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 will call a custom function that will auto-change the entire string as defined by you
    dbms_output.put_line(l_foo);

   --here is another example that will 'allow' you to use the count of the table's position as a pseudo backreference to pull out your items and scrub them as 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;

结果

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}

这是在11gR1中完成的.您可以看到，我只是遍历此过程，并将结果放入varchar2表中，然后从那里逐行替换函数结果. (请注意，可能有更有效的方法来执行此操作，我并不是在追求效率，而只是为了使其发挥作用)

This was done in 11gR1. You can see that I am just looping through this and placing the results into a varchar2 table and then doing the replacement line-by-line from there with the function results. (please note there are probably more efficient ways of doing this, I was not striving for efficiency but rather just to get it to work)

这篇关于是否可以使用函数regexp_replace?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！