该怎么写检查约束:
[AB] + 中,VARCHAR列与非空字符串由A或B的了。
一些限制做的工作,这个简单的没有。 与整理,什么问题?
解决方案SQL服务器本身不支持正则表达式。
然而,可以加入检查约束以匹配所提供的图案
不喜欢'%[^ AB]%'
测试:
声明@Test表(TestColumn为varchar(100),检查(TestColumn不喜欢'%[^ AB]%'和TestColumn!=''))
插入@Test
VALUES('aabab的) - 通过
插入@Test
值(AAB) - 通过
插入@Test
值(AABC) - 失败
插入@Test
VALUES('') - 失败
LIKE
模式是非常有限的。如果你需要真正的正则表达式的限制,你可以实现很简单的CLR函数。有在互联网上大量的例子。例如:普通防爆pressions使模式匹配和数据提取更容易
How to write this check constraint:
[AB]+, varchar column with nonempty strings consisting of A's or B's.
Some constraints do work, this simple doesn't. Problem with collation, or something?
解决方案SQL server does not natively support Regex.
However, it is possible to add check constraint to match the pattern that you provided
not like '%[^AB]%'
Test:
declare @Test table(TestColumn varchar(100) check (TestColumn not like '%[^AB]%' and TestColumn != ''))
insert @Test
values ('AABAB') -- passed
insert @Test
values ('AAB') -- passed
insert @Test
values ('AABC') -- failed
insert @Test
values ('') -- failed
LIKE
patterns are very limited. If you need true Regex constraints you could implement very simple CLR function. There are plenty examples in the internet. For example: Regular Expressions Make Pattern Matching And Data Extraction Easier.