Re: SOS please help with constraint check with like

juan wrote:

> CODPOSTAL is declared CHAR(5)
>
> and i want only put numbers in the 5 positions
>
> thank you.

I already gave you the answer so I don't know why you are still looking. But here it is spelled out.

SQL> create table ziptest (

   2 zipcol varchar2(5));

Table created.

SQL> alter table ziptest

   2 add constraint cc_ziptest_zipcol
   3 check (TRANSLATE(zipcol, '012345678', '999999999') = '99999');

Table altered.

SQL> insert into ziptest

   2 values ('12345');

1 row created.

SQL> insert into ziptest

   2 values ('02398');

1 row created.

SQL> insert into ziptest

   2 values ('12A45');
insert into ziptest
*
ERROR at line 1:
ORA-02290: check constraint (ABC.CC_ZIPTEST_ZIPCOL) violated

SQL> insert into ziptest

   2 values ('12-45');
insert into ziptest
*
ERROR at line 1:
ORA-02290: check constraint (ABC.CC_ZIPTEST_ZIPCOL) violated

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)