Regular Expression in Query or SQL

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 Sun Aug 18 2002 - 20:55:14 CDT