Re: Regular Expression in Query or SQL

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 19 Aug 2002 05:42:25 -0700
Message-ID: <ajqp3h01nnj@drn.newsguy.com>

In article <frj0mus7e82j5oavd88inqpsv9kr9qt9t2_at_4ax.com>, The says...
>
>
>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.

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2200894550208

for a way to load in any regex function however, you won't be using it in a constraint (a trigger yes, constraint, no)

But for something as straight forward as

"^[A-Z][0-9][0-9][0-9]-[A-Z0-9_]*$"

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t   2 ( str varchar2(200)

  3          check ( substr(str,1,1) between 'A' and 'Z'
  4              and
  5              translate(substr(str,2,3),'0123456789','000000000') = '000' )

  6 )
  7 /

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 'A123xxxxxxxxxxxx' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 'A12xxxxxxxxxxxx' ); insert into t values ( 'A12xxxxxxxxxxxx' ) *
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0010386) violated

would be the most performant approach possible.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Aug 19 2002 - 07:42:25 CDT