Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do I enforce this business rule?
On Tue, 19 Jan 1999 20:27:33 -0000, "Jonathan Lewis"
<jonathan_at_jlcomp.demon.co.uk> wrote:
>Would a quicker option be to use translate() twice to translate
>all alpha to 'A' and all numeric to '0' and thn check that the
>result was 'A0A0A0' ?
>
>I can't remember the syntax off-hand, but something like:
>
>check 'A0A0A0' =
>translate(translate (:new.value,'ABC..Z','A'),'0..9',0))
>
>Just an odd thought - doesn't look too convincing though now
>that I've written it out.
That idea works just fine.
eg.
SQL> create table mytable(
2 id number,
3 pcode varchar2(6) check ( 'A0A0A0' =
4 translate( translate( upper(pcode), 5 'BCDEFGHIJKLMNOPQRSTUVWXYZ', 6 'AAAAAAAAAAAAAAAAAAAAAAAAA' ), 7 '123456789', 8 '000000000' ) and length(pcode) = 6 ) )9 /
SQL> insert into mytable values ( 1, 'q1q1a2' ); 1 row created.
SQL> insert into mytable values ( 1, 'q1q1a' ); insert into mytable values ( 1, 'q1q1a' )
*ERROR at line 1:
SQL> insert into mytable values ( 1, 'q1q1aa' ); insert into mytable values ( 1, 'q1q1aa' )
*ERROR at line 1:
Hope this helps.
chris.
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
>
>Jurij Modic wrote in message <36a3ae8e.1331814_at_news.arnes.si>...
>>On Mon, 18 Jan 1999 17:13:29 GMT, kshave_at_health.gov.mb.ca wrote:
>>
>>>I have a table such as the following ...
>>>
>>>TABLE A
>>>name VARCHAR2(25),
>>>address VARCHAR2(25),
>>>city VARCHAR2(25),
>>>pcode VARCHAR2(6)
>>>
>>>The pcode field is supposed to be a postal code in the format
>>>
>>>A9A9A9 where A is any letter A through Z, and 9 is any digit
>>>0 through 9.
>>>
>>>Without creating INSERT/UPDATE triggers, is there any way to
>>>enforce this business rule? Would a check constraint work?
>>
>>Yes, you can use check constraint to enforce this business rule.
>>
>>ALTER TABLE A ADD CONSTRAINT check_pcode CHECK
>> (SUBSTR(pcode,1,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,2,1) BETWEEN '0' AND '9' AND
>> SUBSTR(pcode,3,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,4,1) BETWEEN '0' AND '9' AND
>> SUBSTR(pcode,5,1) BETWEEN 'A' AND 'Z' AND
>> SUBSTR(pcode,6,1) BETWEEN '0' AND '9')
>>
>>>-Keith
>>
>>HTH,
>>
>>Jurij Modic <jmodic_at_src.si>
>>Certified Oracle7 DBA (OCP)
>>================================================
>>The above opinions are mine and do not represent
>>any official standpoints of my employer
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.