Thursday, September 12, 2013

mdq.regex CLR Functions -- Part 3: Regex Check Constraints!


This is the third article in my series, mdq.regex CLR Functions. In my first two articles we reviewed mdq.RegexIsMatch and mdq.RegexMatches. In each article I demonstrated how to use these CLR functions to pass regular expressions (regex for short) to your T-SQL queries. Today I will show you how to create a couple tables with check constraints that use regex to evaluate input using mdq.RegexIsMatch.


Setting up mdq.RegexIsMatch is extremely quick and simple provided you have the right credentials. See this article for detailed instructions on how to setup mdq.RegexIsMatch.

Sample Regex Check Constraints

Creating check constraints to test for tricky string patterns such as a valid IP Address or E-mail address can be a headache using T-SQL. Luckily, regex is perfect for this sort of task; below are two examples. In the first example we create a table with a column that only accepts valid E-mail addresses, the second only accepts valid IP addresses. This is easiest to learn by example so let's get to it...

Valid E-mail Address


(      cust_name     varchar(100),

       cust_email    nvarchar(200)

              CHECK (





Valid IP Address (no leading zeros)


(      computer_name varchar(100),

       ip_address varchar(20)

              CHECK (






As you can see, these mdq.regex functions add a whole new world of possibilities to your SQL Server instance. Thanks for reading!


The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')

Last Updated: 9/12/2013 (Posted)