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. 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
  3. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in ORACLE FINANCE TECHNICAL, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on ORACLE FINANCE TECHNICAL We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete
  4. Simply wish to say your article is as astonishing. The clarity in your post is simply great, and I could assume you are an expert on this subject. Same as your blog i found another one Oracle Fusion Procurement .Actually I was looking for the same information on internet for Oracle Fusion Procurement and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.

    ReplyDelete