Tuesday, March 6, 2012

REGEXP_INSTR function for oracle 9i

I created a procedure which used all these functions in Oracle 10g
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR

Now i want to use the same procedure in Oracle 9i . Then i realized we don't have the above functions in Oracle 9i database. Then googled for string functions available in Oracle 9i. I come to know there's a package called OWA_PATTERN which provides few pattern matching facility. It didn't fulfill my needs. so my further googling gives me a link in which i found function script for REGEXP_LIKE & REGEXP_REPLACE.

so,i started writting code for REGEXP_INSTR.

CREATE OR REPLACE FUNCTION REGEXP_INSTR(LC_IN_STR VARCHAR2,LC_PATTERN VARCHAR2)
return number is
i NUMBER;
LC_LENGTH_STR number;
ln_number_pos NUMBER;
begin
  LC_LENGTH_STR:=length(LC_IN_STR);
 for i in 1..LC_LENGTH_STR
 LOOP
    ln_number_pos:=OWA_PATTERN.AMATCH(lc_in_str,i,lc_pattern);
    EXIT when ln_number_pos>0;
 end LOOP;
 return ln_number_pos-1;
end;
/

The Function Usage Example :
1) SELECT regexp_instr(banner,'[0-9]')
    FROM v$version where banner='Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production';
      Returns : 7

2)SELECT regexp_instr(banner,'[A-Z]')
   FROM v$version where banner='Oracle9i Enterprise Edition Release   9.2.0.6.0 - 64bit Production';
      Returns : 1