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

T-SQL
1

create table blatest(code char(3))
create table blatest(code char(3))

now add the check constraint

T-SQL
1
2
3

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

Now we can run some tests

T-SQL
1
2
3
4

insert blatest values('a12') --fails
insert blatest values('M12')  --good
insert blatest values('D12') --good
insert blatest values('DA1') --fails
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

T-SQL
1
2

select * from ::fn_helpcollations()
where name = 'SQL_Latin1_General_CP1_CS_AS'
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

T-SQL
1
2

alter table blatest drop  constraint ck_bla
go
alter table blatest drop  constraint ck_bla
go

Now we will create the new case sensitive constraint

T-SQL
1
2
3

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

insert blatest values('D12') --good
insert blatest values('d12') --fails
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.

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Related Posts