SQL Constraint for Canada Postal Code + US Zip Code?

Question

I've created a constraint for Canadian postal code and it works fine, however when I input a US zip code like 1234567, the record still gets added in. I'm looking for a way to change my constraint so it only accepts 5 numbers ?

Postal_Code varchar2(7) Constraint NN_Investor_PostalCode Null,
Constraint CK_Investor_PostalCode check 
(REGEXP_LIKE (Postal_Code, '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]') 
    or REGEXP_LIKE (Postal_Code, '[1-9][0-9][0-9][0-9][0-9]')),

US post codes can also be in the format 99999-9999 (called zip+4). Make sure you take that into account. https://en.wikipedia.org/wiki/ZIP_Code — Gary_W, Sep 21 '18 at 17:18

Barbaros Özhan · Answer 1 · 2018-09-21T07:33:44.207

You may try this

REGEXP_LIKE (Postal_Code, '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]') 
        or ( REGEXP_LIKE (Postal_Code, '^[1-9][0-9]{4}') and length(Postal_Code)=5

as the check constraint.

Sample demonstration :

with t(Postal_Code) as
(
 select '12345'   from dual union all
 select '32045'   from dual union all 
 select '1234567' from dual union all
 select '123456'  from dual union all
 select  '01234'  from dual 
)
select * 
  from t
 where REGEXP_LIKE (Postal_Code, '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]') 
    or ( REGEXP_LIKE (Postal_Code, '^[1-9][0-9]{4}') and length(Postal_Code)=5 );

POSTAL_CODE
----------- 
  12345
  32045

SQL Constraint for Canada Postal Code + US Zip Code?

1 Answers1