邮政编码使用 10 位字符的约束检查

问题描述

我有一个带有 Char(10) 列类型的表，名为 postal Code，我需要对所有值进行约束检查，例如 1234567890 没有别的，我使用以下内容:

I have a table with a Char(10) column type, named postal Code and I need a Constraint check for all values just be 10 digits like 1234567890 and nothing else, I use the following:

CONSTRAINT [CH_PCDigit] CHECK ( [PostalCode] LIKE '%[^0-9]%'),
CONSTRAINT [CH_PCLength] CHECK ( LEN([PostalCode])=10)

但不能正常工作，为什么?你的建议是什么?有没有办法将这两个约束与一个合并?

but not worked correctly, why? and what is your suggestion? is there any way to merge this 2 constraint with one?

如果我想要这样的邮政编码怎么办:12345-54321 意思是:5digit-5digit?(类型也必须是 Char(11)).

And what about if I want a Postal Code like this: 12345-54321 mean: 5digit-5digit? (Also Type must be Char(11)).

有没有人知道 SQl 中 Rgex 或约束检查的任何好的来源?

Does any one know any good source for Rgex or Constraint Check in SQl?

推荐答案

SQL Server TSQL 不支持完整的 RegEx.您可以像这样在单个约束中执行您想要的操作:

SQL Server TSQL does not support full blown RegEx's. You can do what you want in a single constraint like so:

CONSTRAINT [CH_PCDigit] 
    CHECK ([PostalCode] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

或更好:

CONSTRAINT [CH_PCDigit] 
    CHECK ([PostalCode] LIKE REPLICATE('[0-9]', 10))

如果你想允许破折号:

CREATE table  ChkTest
(
    PostalCode char(10) not null
        CONSTRAINT [CH_PCDigit]  
    CHECK ([PostalCode] LIKE REPLICATE('[0-9,-]', 10)) 
)

-- Test Code...

insert into ChkTest 
select '1234567890'

insert into ChkTest 
select '123456780'

insert into ChkTest 
select '12345678y0'

insert into ChkTest 
select '12345678901'

select * from ChkTest

insert into ChkTest 
select '12345-8901'

这篇关于邮政编码使用 10 位字符的约束检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！