• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3437
  • Last Modified:

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




0
pinki3
Asked:
pinki3
1 Solution
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now