SQL Server - constraint on values in a field, using search expression

-- using a patindex expression to restrict the values in a field:
-- checks that chars are all alphabetic:
-- CK_tblPRSLicence_LClass ===========================================
ALTER TABLE [dbo].[tblPRSLicence]
DROP CONSTRAINT CK_tblPRSLicence_LClass
GO
ALTER TABLE [dbo].[tblPRSLicence]  WITH CHECK ADD  CONSTRAINT [CK_tblPRSLicence_LClass] CHECK  ((patindex('%[^a-zA-Z_0-9]%',isnull(ltrim(rtrim([LClass])),'')) = 0))
GO
ALTER TABLE [dbo].[tblPRSLicence] CHECK CONSTRAINT [CK_tblPRSLicence_LClass]
GO

note: patindex does NOT use regular expressions (although they look quite similar).  the syntax is documented here:

http://msdn.microsoft.com/en-us/library/ms187489.aspx


--simpler way using LIKE - but this is a looser match:

-- CK_tblPRSLicence_LClass ===========================================
--ALTER TABLE [PRS].[tblPRSLicence]  DROP CONSTRAINT CK_tblPRSLicence_LClass
GO
ALTER TABLE [PRS].[tblPRSLicence]  WITH CHECK ADD  CONSTRAINT [CK_tblPRSLicence_LClass] CHECK  (LClass like '%[AB]%')
GO
ALTER TABLE [PRS].[tblPRSLicence] CHECK CONSTRAINT [CK_tblPRSLicence_LClass]
GO

--this will allow for values A, B, AB, A?, B?, BA, ?A, ?B

note: another way to match, is to use CLR assembly within SQL Server 2008 - then you could use .NET's support for proper Regular Expressions, which are much more powerful than SQL Server search expressions.