Re: SOS please help with constraint check with like

a last question:

could you say me the complete syntax of TRANSLATE?

because in other case, that not all positions will be numbers, i don't know use TRANSLATE.

thank you.

"Daniel Morgan" <damorgan_at_x.washington.edu> escribió en el mensaje news:1076267931.724788_at_yasure...
> 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)
>
Received on Sun Feb 08 2004 - 13:39:31 CST