Oracle. Not valid ascii value of regex result

Question

I'd like to edit a string. Get from 2 standing nearby digits digit and letter (00 -> 0a, 01 - 0b, 23-> 2c etc.)
111324 -> 1b1d2e. Then my code:

   set serveroutput on size unlimited

declare
  str varchar2(128);

  function convr(num varchar2) return varchar2 is
  begin
   return chr(ascii(num)+49);
   --  return chr(ascii(num)+49)||'<-'||(ascii(num)+49)||','||ascii(num)||','||num||'|';
  end;

  function replace_dd(str varchar2) return varchar2 is
  begin
    return regexp_replace(str,'((\d)(\d))','\2'||convr('\3'));
  end;
begin
  str := '111324';
  Dbms_Output.Put_Line(str);
  Dbms_Output.Put_Line(replace_dd(str));
end;

But I get the next string: '112'.

When I checked result by commented return string I'v got: '1<-141,92,1|1<-141,92,3|2<-141,92,4|'.

ascii(num) does not depend on num. It always works like ascii('\'). It is 92, plus 49 we got 141 and it is out of ascii table. But num by itself is printed correctly. How can I get correct values? Or maybe another way to resolve this issue?

I get <-141,92,\3| from the commented out line. The literal \3 seems to be being passed, rather than the backreference you expect. 112 is what you get from just regexp_replace(str,'((\d)(\d))','\2'), and convr is getting something unprintable. — Alex Poole
– Alex Poole, Commented Aug 24, 2016 at 17:16

score 2 · Accepted Answer · 2016-08-24 19:26:38Z

What is happening is that the replacement string is expanded first, and only after it is fully processed, any remaining backreferences like \2 are replaced by string fragments. So convr('\3') is processed first, and at this stage '\3' is a literal. ascii() returns the ascii code of the FIRST character of whatever string it receives as argument. So the 3 plays no role, you only get ascii('\') as you noticed. Then your user-defined function is evaluated and plugged back into the concatenation... by now there is no \3 left in the replacement string.

Exercise: Try to explain/understand why

regexp_replace('abcdef', '(b).*(e)', '\2' || upper('\1'))

is aebf and not aeBf. (Hint: what is the return from upper('\1') by itself, unrelated to anything else?)

You could split the input string into component characters, apply your transformation on those with even index and combine the string back (all in SQL, no need for loops and such). Something like this (done in plain SQL, you can rewrite it into your function if you like):

with
     inputs ( str ) as (
       select '111324' from dual union all
       select '372'    from dual
     ),
     singletons ( str, idx, ch ) as (
       select str, level, substr(str, level, 1)
       from   inputs
       connect by level <= length(str)
       and prior str = str
       and prior sys_guid() is not null
     )
select str, 
       listagg(case mod(idx, 2) when 1 then ch else chr(ascii(ch)+49) end, '')
                  within group (order by idx)
       as modified_str
from singletons
group by str
;

STR    MODIFIED_STR
------ --------------
111324 1b1d2e
372    3h2

Den - Ben · Accepted Answer · 2016-08-26 10:01:18Z

Here code adds 5 to a single letter and resolve the isssue.

set serveroutput on size unlimited

declare
  str varchar2(128);
  str1 varchar2(128);
  function replace_a(str varchar2) return varchar2 is 
  begin
    return regexp_replace(str,'(\D)','5\1');
  end;

  function convr(str varchar2) return varchar2 is
    ind number;
    ret varchar2(128);
  begin
  Dbms_Output.Put_Line(str);
    --return chr(ascii(num)+49)||'<-'||(ascii(num)+49)||','||ascii(num)||','||num||'|';
   ind := 1 ;
   ret :=str;
   loop
     ind := regexp_instr(':'||ret,'(#\d#)',ind) ;
     exit when ind=0;
     Dbms_Output.Put_Line(ind);
     ret := substr(ret,1,ind-2)||chr(ascii(substr(ret,ind,1))+49)||substr(ret,ind+2);
     SYS.Dbms_Output.Put_Line(ret);
   end loop;
   return ret;
  end;

  function replace_dd(str varchar2) return varchar2 is
  begin
    return convr(regexp_replace(str,'((\d)(\d))','\2#\3#'));
  end;
begin
  str := '11a34';
  Dbms_Output.Put_Line(str);
  Dbms_Output.Put_Line(replace_a(str));
  Dbms_Output.Put_Line(replace_dd(replace_a(str)));
end;

result:

11a34
115a34
1#1#5a3#4#
3
1b5a3#4#
7
1b5a3e
1b5a3e

Collectives™ on Stack Overflow

Oracle. Not valid ascii value of regex result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related