加拿大邮政编码 + 美国邮政编码的 SQL 约束?

Question

我已经为加拿大邮政编码创建了一个约束，它工作正常，但是当我输入像1234567这样的美国邮政编码时，记录仍然被添加进来。我正在寻找一种方法来更改我的约束，以便它只接受5数字？

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]')), 

Answer 1

你可以试试这个

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

作为检查约束。

示例演示：

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