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

Intro

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.

Setup

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),

       cust_email    nvarchar(200)

              CHECK (

                     mdq.RegexIsMatch([Cust_Email],

                     '^(([a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+(\.[a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+)*){1,64}@(([A-Za-z0-9]+[A-Za-z0-9-_]*){1,63}\.)*(([A-Za-z0-9]+[A-Za-z0-9-_]*){3,63}\.)+([A-Za-z0-9]{2,4}\.?)+){1,255}$',0)=1

                       )

);

Valid IP Address (no leading zeros)

CREATE TABLE Computers

(      computer_name varchar(100),

       ip_address varchar(20)

              CHECK (

                     mdq.RegexIsMatch([ip_address],

                     '^(?:(?:25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9]?[0-9])\.){3}(?:25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9]?[0-9])$',0)=1

                       )

);

Conclusion

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

--ab

Last Updated: 9/12/2013 (Posted)