Re: Question about check constraint

From: Barry P. Grove <grovebg_at_iSTAR.ca>
Date: 1997/05/21
Message-ID: <5ltl20$r55@news.istar.ca>#1/1

tomas_at_senna.std.lt wrote:
>
> Does anybody know is it possible create check constraint that restrict
> updating or inserting into table columns of varchar2 type values that
> have numbers or others symbols ex: test11 not allowed test[ not allowed
> test allowed
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Hi,

You might try this example using the translate function (and assuming upper and
lower case alpha are valid):

  TRANSLATE(UPPER(column_name),

            'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
            'AAAAAAAAAAAAAAAAAAAAAAAAAA') =

RPAD('A',LENGTH(column_name),'A')

All alphabetic chars are converted to the letter 'A', which should match a
reference string of 'A's padded out to the same length. Any non-alpha chars
are not translated and will not match the reference string.

This example won't handle null strings correctly.

I use a similar idea to validate Canadian postal codes of the form A9A-9A9:

  TRANSLATE(UPPER(postal_code),

            'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
            'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999') = 'A9A-9A9'

Using a single letter to represent letter groups simplifies input validation.

-- 
Barry P. Grove                          BarrySoft Systems Applications
grovebg_at_iSTAR.ca                        (604)929-5433
Developer, Oracle DBA, Unix Sysadmin    North Vancouver, BC, V7H-2G4

Received on Wed May 21 1997 - 00:00:00 CDT