XML FeedsSomeone 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.
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Nice - a clean, clear explanation using 'like'. 
People so often forget about check constraints entirely 
I agree, I have seen code that runs once an hour to convert NULLs to 0.......why not make the column not null instead or put a constraint that checks that the column is greater or equal to 0
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.