Equivalent of PATINDEX in oracle for pattern searching

I am working on ORACLE9i. I want equivalent of PATINDEX in ORACLE. ORACLE provides a function INSTR but it does not support wild characters. I want to search the strings using wild characters, which INSTR does not support...

Is there any equivalent of PATINDEX in oracle so that i can search patterns using wild characters??? If not function then is there any package which provides a similar functionality.

Thanks
Alok




pinki3Asked:
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.

anand_2000vCommented:
You can create a function based index from Oracle 8i onwards.....You can use any methodology in the PL/SQL part to implement the same.
0
schwertnerCommented:
You have to wait for Oracle 10g which supports regular expressions.
0
anand_2000vCommented:
Schwertner: A doubt....
Is my answer correct....I believe so ...but always am ready to get it confirmed from an expert..:)
0
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!

grim_toasterCommented:
Strictly theoretical I'm afraid, but you could (???) load a java object into the database, and using the regexp package available in JDK1.4 (if it's available to your Oracle instance?) use Java's regular expressions in a call from a PL/SQL function.
0
pinki3Author Commented:
Is it mean that currently we don't have any built in function or package in ORACLE which might work for pattern matching using wild characters????

Thanks

0
grim_toasterCommented:
It would all depend on how advanced you want it to be.  You could use the standard _ or % characters to a certain degree to get any character/characters, but to my knowledge there is no package (unless there's one on the internet somewhere...) that does regular expressions.
0
monu85Commented:
AFAIK oracle doesn't provide any built-in function or package equivalent to PATINDEX. So i  think you will have to code a function equivalent to PATINDEX which might be 100-200 lines...

try to survive with INSTR if u can else code the function..

monu
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
alokguptCommented:
i think oracle supports some package, can any body let me know the exact name of the package...

0
anand_2000vCommented:
>>You can create a function based index from Oracle 8i onwards.....You can use any methodology in the PL/SQL part to implement the same.

>>AFAIK oracle doesn't provide any built-in function or package equivalent to PATINDEX. So i  think you will have to code a function equivalent to PATINDEX which might be 100-200 lines...

>>try to survive with INSTR if u can else code the function..


WHAT WAS THE DIFFERENCE? except longer sentences?
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.