Adding upper case letter only constraint in SQL Server

Question

create table EmployeeStatistics  
(  
     EmployeeIdNo int,  
     Name varchar(30),  
     Salary integer,  
     Benefits integer,  
     Position varchar(25),  
     Allowance integer,   
     DateOfBirth integer,  
     Telephone integer,     
     primary key (EmployeeIdNo)  
);

I want Position to accept only upper case letters. Which constraint to add?

alter table EmployeeStatistics
#add constraint

Answer 1

You can use a check constraint where you compare the value with the upper case value using a binary collation or a case sensitive collation.

alter table dbo.EmployeeStatistics 
  add constraint CK_EmployeeStatistics_Position
  check (upper(Position) = Position collate Latin1_General_BIN2)