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

No comments:

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.