Someone posted the following question
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.
Someone posted the following answer
You cannot do this out of the box – MS SQL Server does support CHECK CONSTRAINTS – but for things like a maximum or minimum INT value, or a string length or such.
What you’re looking for would be a CHECK based on a regular expression – and out of the box, SQL Server does not offer that capability.
You could theoretically write a .NET assembly, deploy it inside SQL Server, and then use it to enforce the CHECK – not a trivial undertaking.
While SQL server does not support a full implementation of regular expression, you can do what the person asked for without a problem in T-SQL.
Here is what the regular expression looks like [DMOPT][0-9][0-9], that will alllow allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.
Enough talking let’s look at some code, first create this table
create table blatest(code char(3))
now add the check constraint
alter table blatest add constraint ck_bla check (code like '[DMOPT][0-9][0-9]' ) GO
Now we can run some tests
insert blatest values('a12') --fails insert blatest values('M12') --good insert blatest values('D12') --good insert blatest values('DA1') --fails
As you can see we got the following message twice
_Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint “ck_bla”. The conflict occurred in database “Test”, table “dbo.blatest”, column ‘code’.
The statement has been terminated.
If you want to insert D12 but not d12, in other words you need the constraint to be case sensitive then you have to create the constraint like this
check (code like ‘[DMOPT][0-9][0-9]’ COLLATE SQL_Latin1_General_CP1_CS_AS )
What we did is used the SQL_Latin1_General_CP1_CS_AS collation, to find out what this collation does, run the following
select * from ::fn_helpcollations() where name = 'SQL_Latin1_General_CP1_CS_AS'
Here is what is returned as the description
Latin1-General, case-sensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data
Let’s create the constraint, first we need to drop the old constraint
alter table blatest drop constraint ck_bla go
Now we will create the new case sensitive constraint
alter table blatest add constraint ck_bla check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) GO
insert blatest values('D12') --good insert blatest values('d12') --fails
The insert with D12 will succeed but the one with d12 will not.
As you can see you can use regular expressions in check constraints, there is no need to use a trigger in a case like this.