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
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