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

4 comments:

  1. REGEXP_SUBSTR ANYBODY HAVE A DLL FUNCTION

    ReplyDelete
  2. I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete
  3. erptree training institute is one of the best oracle fusion procurement training center in Hyderabad provide training on all oracle fusion modules with real time experts across the world like USA UK India Australia
    thank regards
    Oracle Fusion procurement Coaching
    Oracle Fusion procurement Training Institute

    ReplyDelete