Creating CLR SQL User Defined Function to Validate Values Using Regular Expression.

2008-07-04T04:49:00Z

This article will explain on how to validate your values using a regular expression in a user defined SQL function. SQL server does not provide any function for this purpose but we can write a CLR enabled function to fulfill our purpose.

Step1: Write code in .Net

The following code will create a .Net SQL function that will validate values on the basis of a regular expression. Create a new Class library project with the name ClrFunctions. Add a new class (Functions) to the project and copy / paste the code below to the class file.

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

namespace ClrFunctions

{

public partial class Functions

{

/// <summary>

/// Validates a value based on expression pattern.

/// </summary>

/// <param name="value">input to be checked</param>

/// <param name="pattern">regular expression</param>

/// <returns></returns>

[SqlFunction(Name="RegEx")]

public static SqlBoolean RegEx(SqlString value, SqlString pattern)

{

Regex ObjRegx = new Regex((string) pattern);

Match ObjMatch = ObjRegx.Match((string) value);

return (SqlBoolean)ObjMatch.Success;

}

The SqlFunction attribute indicates the method should be registered as a function. The Name property represents the name under which the function should be registered in SQL Server. This attribute is used only by Microsoft Visual Studio to register the specified method as a user-defined function automatically however it is not used by SQL Server.

Compile your project in release mode. You will now notice that there will be a DLL in your \bin\Release folder.

Step2: Using the Function in SQL Server

Once you have compiled your dll do the following to import your dll into SQL Server.

2.1 Enable CLR.

SQL server is not configured to use CLR code by default. To enable CLR write the following code in SQL query analyzer and press F5 (execute).

sp_Configure 'CLR Enabled', 1 Reconfigure With Override

You can also enable CLR from surface area configuration tool provided by SQL Server 2005.

2.2 Write Code to Import your DLL.

Create Assembly ClrFunctions From 'E:\MyProjects\ClrFunctions\Bin\Release\ClrFunctions.dll'

-- Note: Change the path according to the location of your DLL.

2.3 Write Code to Create the User Defined Function.

Create Function dbo.RegEx(

@Input As nvarchar(4000),

@Expression As nvarchar(1000)

)

Returns Bit

As External Name [ClrFunctions].[ClrFunctions.Functions].[RegEx]

The first portion of [ClrFunctions].[ClrFunctions.Functions].[RegEx] is the name of assembly registered in SQL server. The second poison i.e. [ClrFunctions.Functions] represents the namespace and type name and the last portion is the function name.

Step3: Test Your Function

Once you are successful in configuring you DLL. Use the following statement to test your function. The example below will test the email format supplied.

Select dbo.RegEx('FarhanUddinKhan@Hotmail.com', '\w+([-+.'']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*')

Unloading DLL from SQL Server

You may want to unload the .Net DLL and function that you created in SQL Server. In this case do the following. First drop all the references to the dll and then drop the assembly.

Drop Function dbo.RegEx

Drop Assembly ClrFunctions

Note: For more information on creating CLR enabled user defined functions please visit the following URL’s.

http://sql-server-performance.com/articles/dev/clr_2005_p1.aspx

http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx

http://technet.microsoft.com/en-us/library/ms131050.aspx

Farhan Uddin Khan

Creating CLR SQL User Defined Function to Validate Values Using Regular Expression.