Re: Question about check constraint

From: Ari Kaplan <akaplan_at_psycfrnd.interaccess.com>
Date: 1997/05/19
Message-ID: <5lqbel$5gt@psycfrnd.interaccess.com>#1/1

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:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

tomas_at_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
 
>-------------------==== Posted via Deja News ====-----------------------
>      http://www.dejanews.com/     Search, Read, Post to Usenet
 

Received on Mon May 19 1997 - 00:00:00 CDT