SQL 邮政编码使用 10 位字符的约束检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9375767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Constraint Check for 10 Digit Character use for Postal Code
提问by Saeid
I have a table with a Char(10)column type, named postal Codeand I need a Constraint check for all values just be 10 digits like 1234567890and nothing else, I use the following:
我有一个Char(10)名为列类型的表，postal Code我需要对所有值进行约束检查，例如只有 10 位数字1234567890，没有别的，我使用以下内容：
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?
但工作不正常，为什么？你的建议是什么？有没有办法将这两个约束与一个合并？
And what about if I want a Postal Code like this: 12345-54321mean: 5digit-5digit? (Also Type must be Char(11)).
如果我想要这样的邮政编码12345-54321怎么办：意思是：5digit-5digit？（也类型必须是Char(11)）。
Does any one know any good source for Rgex or Constraint Check in SQl?
有没有人知道 SQl 中 Rgex 或约束检查的任何好的来源？
回答by Mitch Wheat
SQL Server TSQL does not support full blown RegEx's. You can do what you want in a single constraint like so:
SQL Server TSQL 不支持完整的 RegEx。您可以在单个约束中执行您想要的操作，如下所示：
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]')
or better:
或更好：
CONSTRAINT [CH_PCDigit]
CHECK ([PostalCode] LIKE REPLICATE('[0-9]', 10))
If you want to allow dashes:
如果你想允许破折号：
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'
回答by Tony
Here is one that accepts both U.S. Zip Code and Canada Postal Code.
这是一种同时接受美国邮政编码和加拿大邮政编码的方法。
CONSTRAINT CH_PCDigit
CHECK (PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]' OR
PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
PostalCode LIKE '[A-Y][0-9][A-Z][0-9][A-Z][0-9]')
回答by dani herrera
YOu can use isnumeric, split big number:
您可以使用 isnumeric，拆分大数：
CREATE TABLE a (
pc CHAR(10),
CONSTRAINT pc_c CHECK (
LEN(pc) = 10 AND
ISNUMERIC(SUBSTRING(pc,1,5))=1 AND
ISNUMERIC(SUBSTRING(pc,6,5))=1)
)
