Does oracle 9iR2 support regular expression with OWA_PATTERN?

I think 10g has mainly 4 function like REGEXP_LIKE< REGEXP_INSTR, REGEXPR_SUBSTR, REGEXP_REPLACE
that do not seem wupported in 9i.

How would you do something like this in 9i

SELECT REGEXP_COUNT('567567567567567', '(56)7', 5, 'i') count FROM DUAL

I wonder if oracle will support perl regular epxressions packages.

Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
OK, using an example from the link you provided you cannot come up with an example?

There is a function in:

That basically writes this for you.
create or replace function myCount(inStr in varchar2, inPattern in varchar2) return number
 num_found number;
 myStr varchar2(4000) := inStr;
 num_found := owa_pattern.change(myStr, inPattern, '0', 'g');
 return num_found;

show errors

select myCount('567567567567567', '(56)7') from dual;

Open in new window

sdstuberConnect With a Mentor Commented:
no,  regular expressions aren't supported until 10g

regexp_count specifically isn't supported until 11g
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

slightwv (䄆 Netminder) Commented:
I should add that this probably will be handle all patterns you throw at it.  I'm not familiar with that package and do not know how compatible it is.
sam15Author Commented:
I asume you cant use OWA_PATTERN directly with SQL and you haave t ocreate user defined function?

Does 10 has 4 additional REGEXP function you can use with SQL?
slightwv (䄆 Netminder) Commented:
If you look up the package, owa_pattern.change has OUT parameters.  So, no you cannot use it in normal SQL.

As mentioned above, 10g, introduces native regex support.  The docs have what is available and when.
as noted above 10g offers "some" regexp support, but not regexp_count


11gR1 -offers REGEXP_COUNT as well as sub-expression support for the REGEXP_INSTR and REGEXP_SUBSTR functions

See the SQL Reference for specifics
sam15Author Commented:
ok, great one small thing. do you know what this pattern is searching for.

this is an old code in another language i am converting.
slightwv (䄆 Netminder) Commented:
Actually just the numbers 567.  The parenthesis in this context don't mean anything.

You are counting the number of times 567 appear in the string.
slightwv (䄆 Netminder) Commented:
I guess I should have added: parans create a 'group'.

So you are asking; look for a 7 followed by the group of 56.
>>> 7 followed by the group of 56

other way around,

group of 56 followed by 7
slightwv (䄆 Netminder) Commented:
>>group of 56 followed by 7

Yes.  Thanks for the correction.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.