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

SharePoint: All about Features - Editing Tips

This article is one of a series. See All about Features! for more.

 

Tips for editing Features

Features are defined using XML. You can use any editor, but likely candidates include Notepad, Visual Studio, SharePoint Designer and any other XML editor. To edit XML with some form of Intellisense requires an XML schema. SharePoint supplies one named WSS.XSD and it can be found in:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\wss.xsd

The wss.xsd schema defines: Feature, Elements, List, Project and Solution.

In many XML editors you register the schema in the editor and then list the schema in the XML tag:
<Feature  xmlns="http://schemas.microsoft.com/sharepoint/">
</Feature>

In Visual Studio you can enter the path to the schema file in the Schemas property of the document. Once the editor knows the schema you should get syntax help from your XML editor.

 

Back to All about Features! for more.

SharePoint: All about Features

 

All about Features!

All? Not really... Features are one of SharePoint's Swiss Army Knives, something for everybody...

So where to start? Here's a list of topics... the ones that are links are finished and the others will be done one of these days!

• Tips for editing Features
• The feature.xml file
• The elements.xml file
• How to deploy a Feature
• Features to add menu items to SharePoint
• Features to remove items from SharePoint
• Features to add custom controls (using SharePoint Delagate controls)
• Features to run custom .Net code
• Features to deploy files