Solved

Equivalent of PATINDEX in oracle for pattern searching

Posted on 2003-11-06
9
2,892 Views
Last Modified: 2007-12-19
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




0
Comment
Question by:pinki3
9 Comments
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9693770
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
 
LVL 48

Expert Comment

by:schwertner
ID: 9693797
You have to wait for Oracle 10g which supports regular expressions.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9693814
Schwertner: A doubt....
Is my answer correct....I believe so ...but always am ready to get it confirmed from an expert..:)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:grim_toaster
ID: 9694077
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
 

Author Comment

by:pinki3
ID: 9699429
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
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9699620
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
 
LVL 1

Accepted Solution

by:
monu85 earned 100 total points
ID: 9699635
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
 

Expert Comment

by:alokgupt
ID: 9712833
i think oracle supports some package, can any body let me know the exact name of the package...

0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9714131
>>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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select unique values from two columns 4 79
oracle- set role and grant privileges 6 50
Procedure syntax 5 48
Migration from sql server to oracle 5 34
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question