Re: How do I enforce this business rule?

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.

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
Received on Tue Jan 19 1999 - 14:27:33 CST