Regular expressions in SQL Server 2008

How to开发者_JAVA百科 write this check constraint:

[AB]+, varchar column with nonempty strings consisting of A's or B's.

Some constraints do work, this simple doesn't.

Problem with collation, or something?

SQL server does not natively support Regex.

However, it is possible to add check constraint to match the pattern that you provided

not like '%[^AB]%'

Test:

declare @Test table(TestColumn varchar(100) check (TestColumn not like '%[^AB]%' and TestColumn != ''))

insert @Test
values ('AABAB')  -- passed

insert @Test
values ('AAB')    -- passed

insert @Test
values ('AABC')   -- failed  

insert @Test
values ('')  -- failed

LIKE patterns are very limited. If you need true Regex constraints you could implement very simple CLR function. There are plenty examples in the internet. For example: Regular Expressions Make Pattern Matching And Data Extraction Easier.

SQL server 2008 doesn't support regular expressions natively. You can write a custom CLR user-defined function that does this. MSDN has a wealth of resources to guide you through the process of creating one. This article, for example.

Additionally, this article seems to cover exactly what you want to do with check constraints. It demonstrates how you can set a CLR UDF as your check constraint.

If you want to use a full functionality regex in MSSQL constraint - you need to write a custom dll and attach it to the sql server. then you will able to register it as a local function and use the regext any way you like.

see http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx.

If you have diffuculties with this - I can post a simple example of working clr library for MSSQL

If using Check constraint given by others above (ie. [AB][AB] etc), does not give you the flexibility to test the varchar value properly then you could try using a trigger.

A trigger will allow you to test what your after more thoroughly.

Refer Example C on this link while its using the trigger to test business rules across tables its just a matter of changing it to suit what ever test you need to do :)

Hope this helps

Sample code to help you along change the testing to suit your needs

CREATE TABLE [dbo].[TestTrigger](
    [stringtest] [varchar](100) NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[TestTrigger_TestAB] 
    ON [dbo].[TestTrigger] 
    FOR INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @testString varchar(100)
    Declare @Len as int;
    Declare @SearchPattern as varchar(8000);
    Declare @Result as Int;

    SELECT @testString = stringtest FROM inserted

    Select @Len = Len(@testString);

    While @Len>0
    Begin
        Select @SearchPattern = Isnull(@SearchPattern,'') + '[A-B]';
        Select @Len = @Len -1;
    End

    Select @Result = Case When @testString Like @SearchPattern Then 1 Else 0 End;

    IF (@Result = 0)
    BEGIN
        RAISERROR ('Value entered did not contain only A or B', 16, 1)
        ROLLBACK TRANSACTION
    END
END


SET ANSI_PADDING OFF
GO

SELECT * FROM t
WHERE REPLACE(REPLACE(col, 'A', ''), 'B', '') = ''