How to set a check on SQL Server?

Question

Hi,

I need to add table called group with a column called code

How do I add a check constraint to the column so it will only allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.

Answer 1

simple check constraint is all you need

create table blatest(code char(3))

alter table blatest add  constraint ck_bla 
check (code like '[DMOPT][0-9][0-9]' )
GO

test

insert blatest values('a12') --fails
insert blatest values('M12')  --good
insert blatest values('D12') --good
insert blatest values('DA1') --fails

If you need it to be case sensitive then you have to create the constraint like this

alter table blatest add  constraint ck_bla 
check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO

D12 will succeed but d12 will not in that case