Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Regular Expression in Query or SQL
Have you though about loading some of the many java packages for
regular expression matching into the server and the put some sort of
PL/SQL interface to it that you can then use in a where clause in your
sql query?
Martin
The C. Family <aurora_at_nr.infi.net> wrote in message news:<frj0mus7e82j5oavd88inqpsv9kr9qt9t2_at_4ax.com>...
> I've searched all over Google for this, and I'm beginning to believe
> that the answer is "no, it can't be done (without inordinate effort)."
>
> I would like to use something more sophistcated than just % and _
> for pattern matching in SQL queries, and especially in check constraints.
>
> I have an application that expects identifiers that are comprised of
> a alpha character, followed by three digits, then a dash and one to
> 120 characters that can an alpha, a digit, or an underscore (all alphas
> are uppercase, thankfully). In a regular expression, I would match
> on "^[A-Z][0-9][0-9][0-9]-[A-Z0-9_]*$" (I realize some regex's allow
> the three digits in a row to be expressed in a slightly more compact
> fashion, but I left it that way for illustrative purposes- and because I
> couldn't remember exactly how)
>
> To do this with Oracle PL/SQL, I would have to have a series of possibly
> hundreds of and/or clauses with parentheses in abundance. I'd like to
> avoid that, especially in the check constraints for columns that contain
> these identifiers. Regular expression parsers do that sort of thing very
> well. I don't.
>
> Is there something I have been missing in my searches and reading of
> the documentation? FWIW, I have done the research before asking the
> group. I'm hoping we aren't going down the wrong technology road for
> our app. It's the one thing that's keeping us from finalizing our RDMS
> choice on Oracle for this application. Otherwise, it's Sybase, and all
> I can say bout that is "Yikes!!!"
>
> You'd think enough people would have asked for this that the major
> RDMS vendors would just dig in an write a regex function. Sheesh.
Received on Mon Aug 19 2002 - 16:11:16 CDT