CHECK Constraint to enforce upper case letter and pattern match

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]'));

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:

• COLLATE

• Collation Precedence

StackExchange Database Administrators Q#284344, answer score: 7