Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SOS please help with constraint check with like

Re: SOS please help with constraint check with like

From: juan <toxic_rain_at_hotmail.com>
Date: Sun, 8 Feb 2004 20:39:31 +0100
Message-ID: <c0639i$1pa$1@nsnmrro2-gest.nuria.telefonica-data.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US