RegulrExpresionsSupport

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.

sam15Asked:
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:
http://www.di.unipi.it/~ghelli/didattica/bdldoc/A97329_03/web.902/a90101/pspatt.htm

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

show errors

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

Open in new window

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


regexp_count specifically isn't supported until 11g
0
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.
0
 
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?
0
 
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.
0
 
sdstuberCommented:
as noted above 10g offers "some" regexp support, but not regexp_count

10gR1 offers --- REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR.


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


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

this is an old code in another language i am converting.
0
 
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.
0
 
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.
0
 
sdstuberCommented:
>>> 7 followed by the group of 56


other way around,


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

Yes.  Thanks for the correction.
0
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.