Case灵敏度检查约束与LIKE操作符使用字符范围通配符

I'm facing a problem in regular expressions case sensitivity. In a regular expression expression only the first item works within the square brackets.

我正面临正则表达式大小写敏感的问题。在正则表达式中，只有第一个项在方括号内工作。

create table
(
    FlowerId varchar(7)

    constraint chk_flid_regex 
    check(ActorId like'[A-Z][a-z][A-Z]' collate sql_latin1_general_CP1_CS_AS)
);

Eg. If i give something like this '[A-Z][a-z][A-Z]' Only the first [A-Z] is checked by the server. The third [A-Z] is not checked.

如。如果我给出这样的“[A-Z][A-Z][A-Z]”，服务器只检查第一个[A-Z]。第三个[A-Z]没有被检查。

So if I insert values like 'Abc' gets inserted while it shouldn't actually get inserted into the table. Whereas, it doesn't give any error. It should only accept characters like 'AbC'.

如果我插入像Abc这样的值，它就会被插入，而它实际上不应该被插入到表格中。然而，它不会给出任何错误。它应该只接受像“AbC”这样的角色。

3 个解决方案

#1

Though I can't explain why the CS collation isn't working, switching to a binary collation seems to behave as you are expecting, at least with Sql Server 2008:

虽然我无法解释为什么CS排序规则不起作用，但切换到二进制排序规则似乎和您预期的一样，至少在Sql Server 2008上是这样的:

create table tbl
(
     FlowerId varchar(7)
constraint chk_flid_regex 
check(FlowerId like'[A-Z][a-z][A-Z]' collate Latin1_General_BIN)
);

Sql Fiddle

Sql小提琴

#2

You can't use the range form, you have to expand out all the letters you want to use:

你不能使用范围表，你必须展开你想要使用的所有字母:

create table
(
    FlowerId varchar(7)

    constraint chk_flid_regex 
    check(ActorId like'[ABCDEFGHIJKLMNOPQRSTUVWXYZ][abcdefghijklmnopqrstuvwxyz][ABCDEFGHIJKLMNOPQRSTUVWXYZ]' collate sql_latin1_general_CP1_CS_AS)
);

Or switch to a binary collation. Why? Because in most collations, the lower case letters are placed between the upper case letters, either before or after their upper case equivalents. So the range A-Z expands out as AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZ - all you've excluded as a letter is lower-case z.

或者切换到二进制排序。为什么?因为在大多数排序中，小写字母被放在大写字母之间，要么放在大写字母之前，要么放在大写字母后面。所以a - z的范围扩展为aabbccddeeffghiijkllmnkllmnnooppqqssttuuvwwxxyyz -你所排除的字母都是小写的z。

(I hope I've got my alphabet right each time I've had to type it out above, but you might want to check carefully that I've not missed out any letters)

(我希望每次我必须把上面的字母打出来的时候，我的字母表都是正确的，但是你可能要仔细检查一下，我没有漏掉任何字母)

#3

^[A-Z][a-z][A-Z]$

Add anchors to disallow partial matches.

添加锚，不允许部分匹配。

#1