3

For one of my columns I am trying to enforce a pattern. The first letter should be D upper case, and the remaining 3 characters should be digits. For example:

D678, D890, D000

I'm quite new with CHECK constraints and things like regular expressions.

Below is what I've done so far, which (I think) enforces the general pattern. However when I try adding something like d900 it works, even though it's a lower case d. I expected this to fail.

Can someone please assist:

CREATE TABLE Systems(
SystemsID NVARCHAR(4),
Title NVARCHAR(30),
CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]'));
2
  • A tangent to your question but you should just use D not [D] – Martin Smith Jan 31 at 2:45
  • Shouldn't SystemsID be declared NCHAR(4) or even CHAR(4)? – Charlieface Jan 31 at 4:24
8

It seems the database and column collations are case-insensitive so the LIKE expression is also case-insensitive.

One way to perform a case-sensitive compare in this scenario is by adding a COLLATE clause, specifying a case-sensitive collation. For example, if your database default collation is a case insensitive collation such as Latin1_General_CI_AS, the example below will override that collation with the case-sensitive version of the collation for the literal and perform the case-sensitive comparison you want:

CREATE TABLE Systems(
    SystemsID NVARCHAR(4),
    Title NVARCHAR(30),
    CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]' COLLATE Latin1_General_CS_AS)
);

Below are related collation documentation pages for your perusal:

2
  • Hi. Thank you. I'll give this a try. I've not come across COLLATE yet so I'll need to look into that first. – Aaron Wright Jan 30 at 17:57
  • @AaronWright, I added documentation links to my answer for your review. – Dan Guzman Jan 30 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.