Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases.oracle.marketplace,comp.databases.oracle
Subject: Re: Question about check constraint
References: <864055106.15780@dejanews.com>

tomas@senna.std.lt writes:

>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

There are several ways to do this:

In the check constraint clause,
1) instr(upper(translate(column_name,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()"-_=+[]/?;:,.<>',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ00000000000000000000000000000000000')),'0') =0

This will be 0 if only characters are used. Otherwise a number > 0 will be
returned and the constraint will prohibit the record to be inserted.

2) Do it the long way:
  column_name not like '%0%' and column_name not like '%1%' and
  column_name not like '%2%' and column_name not like '%3%' and
  column_name not like '%4%' and column_name not like '%5%' ...
  ....
  ....

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 60+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              www.arikaplan.com                                <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page >Back to Ari Kaplan's Home Page