SQL: Sample CLR function for a Regular Expression validation

 

Here is a quick example of a .net CLR function to validate any string value using a regular expression. (only works with SQL 2005 and later of course)

1) create a new project using the C# or VB "SQL Server Project" template

2) add a New Item - a function

3) add this code!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegExpression(string strToTest, string regExpression)
    {
        return Regex.IsMatch(strToTest,regExpression);
    }
};

4) Deploy and test!

 

Samples of how to call:

Test a phone number:

print dbo.RegExpression('223-123-1234','^[2-9]\d{2}-\d{3}-\d{4}$')

Test an email address:

print dbo.RegExpression('abc@maxtrain.com', '^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$')

 

Test a column of phone numbers:

use AdventureWorks
select phone, dbo.RegExpression(phone,'^[2-9]\d{2}-\d{3}-\d{4}$') as validphone  from person.contact