Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3191
  • 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
Technology Partners: 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!

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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