# #0285 – SQL Server – Using Regular Expressions with CHECK constraints

I was recently asked a good question by one of the team members. They wanted to know if it would be possible to control the insertion of values in a table to match a given set of business rules via check constraints. What attracted my attention and prompted me to spend some time with them was the fact that they knew CHECK constraints were the answer, but were unaware about how to use CHECK constraints in this scenario.

The solution that I provided was to use regular expressions in the CHECK constraint definition. Here’s a small example that will guide the reader on using regular expressions in CHECK constraints.

## The example

Assume that the fictional AdventureWorks BiCycle Company has a business rule that all invoices being processed must begin with the letters AWC followed by a 5-digit number beginning at 10000, making the pattern (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer).

We will attempt to achieve this via a simple CHECK constraint. To begin with, we will create a sample temporary table:

```USE tempdb;
GO
–Safety Check
IF OBJECT_ID(‘tempdb..#InvoicesProcessed’) IS NOT NULL
DROP TABLE #InvoicesProcessed;
GO
–Create a dummy table
CREATE TABLE #InvoicesProcessed
(InvoiceNumber VARCHAR(10) NOT NULL,
ProcessDate DATETIME
)
GO
–Add a simple default constraint for the ProcessDate field
ALTER TABLE tempdb..#InvoicesProcessed
ADD CONSTRAINT df_ProcessDate DEFAULT GETDATE() FOR ProcessDate;
GO```

### Using Regular Expressions in a CHECK constraint definition

Now comes time to create the CHECK constraint that will actually implement the business logic. We will be using regular expressions in the constraint. To summarize, the requirements for an Invoice Number are:

• Total length = 8 characters
• 1st 3 characters must be AWC
• Next 5 characters are numbers
• The sequence of numbers must begin from 10000

The pattern therefore is (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer), which translates to the following CHECK constraint:

```USE tempdb;
GO
–Now. add the CHECK constraint with the regular expressions
ALTER TABLE tempdb..#InvoicesProcessed
CHECK (InvoiceNumber LIKE ‘AWC[1-9][0-9][0-9][0-9][0-9]’);
GO```

### The Test

Let us know test the constraint by adding some valid and some invalid values:

```USE tempdb;
GO
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC10021’);
GO
–Failure Reason: 1st numeric value after AWC must be greater than 1
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC00021’);
GO
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘00010021’);
GO
–Failure Reason: Trailing characters are not numeric
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC100AB’);
GO```

As can be seen from the messages tab, we notice that only 1 record was added to the table – the others failed because they did not meet the Invoice Number requirements.

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.

Let us validate the Inserts by SELECTing data from the table.

```USE tempdb;
GO
–Select from the table
SELECT ip.InvoiceNumber,
ip.ProcessDate
FROM #InvoicesProcessed AS ip;
GO```

## Conclusion

Regular Expressions are extremely powerful and CHECK constraints is just one of the many Microsoft SQL Server provides areas where they can be leveraged to enforced the rules required by the business.