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
CREATE TABLE Customers
( cust_name varchar(100),
Valid IP Address (no leading zeros)
CREATE TABLE Computers
( computer_name varchar(100),
As you can see, these mdq.regex functions add a whole new world of possibilities to your SQL Server instance. Thanks for reading!
Last Updated: 9/12/2013 (Posted)