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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
no,  regular expressions aren't supported until 10g


regexp_count specifically isn't supported until 11g
0
slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.