CODEWITHSUNDEEP
sql-server
ANewComer
ANewComer

Reputation:

How to set a check on SQL Server?

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.

Upvotes: 1

Views: 224

Answers (1)

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 7

Related Questions