How to write check constraint to check for uppercase character and digit?

[Azuki]

I want to write a check constraint in SQL Server to check that the ID is exactly 5 characters long, starts and ends with an UPPER case an there are 3 digit in between. Do I use a LIKE clause? Or how?

I tried using
CONSTRAINT ValidID CHECK (clientID LIKE '[A-Z][0-9][0-9][0-9][A-Z]')

But if I do this, and I insert a id 'a123e' (starting and ending with small capital letter, it will still let me go through. By right, it should forbid me to do so.

[ck9663]

Could be because your sql server is configured to ignore cases.

Good Luck!!!


~~ CK

[Rabbit]

ck is correct, by default, SQL Server will ignore case. Change the collation of the column to Latin1_General_ CS_AS. That will make it case sensitive.