Use Regular Expression in SQL Server by using .Net CLR Integration

Regular expression is frequently used in today’s development for various purposes such as validation field, email address validation, set password criteria etc. Dot net supports regular expression also. You can use it in your application easily by referencing the assembly ‘System.Text.RegularExpressions’ . But if the situation occurs when you need to use regular expression in SQLServer then how can you do that? In this article I will show you the way how you can do it.

Just follow the procedure:

Part 1

1.      Start your visual studio.

2.      Create a new Class Library Project. My case it is ‘RegxWithSQLServer’

3.      Add a new C# class to RegxWithSQLServer project. My case it is ‘RegularExpressions.cs’

4.      After that your solution explorer looks like (Ignore Client and MyService project)

Now open your RegularExpression.cs class (J) and make it public. Then add these three assemblies in your class

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using System.Data.SqlTypes;

To add the reference of Microsoft.SqlServer.Server assemblies add the reference Microsoft.SqlServer.Types and then add the reference to your class.

Now I add one method in my class named ‘RegEXIsMatch’ whose purpose is to take two parameter and match the input string to the regular expression pattern and return 0 or 1 as the result of match. The method finality looks like

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true,IsPrecise = true)]

public static SqlBoolean RegEXIsMatch(SqlString inputString, SqlString  RegxExpressionPattern)

{

      if (inputString.IsNull || RegxExpressionPattern.IsNull)

      {

           return SqlBoolean.False;

      }

      return Regex.IsMatch(inputString.Value, RegxExpressionPattern.Value, RegexOptions.IgnoreCase);

}

Let us look at the method code.

1.       It uses the attribute Microsoft.SqlServer.Server.SqlFunction. The purpose of the attribute is to mark a method definition of a user-defined aggregate as a function in SQL Server[from msdn]. For more information about the attribute visit http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlfunctionattribute.aspx.

2.      It takes two parameter inputString and RegxExpressionPattern. Then using the IsMatch method of Regex class determine it the input string match to the pattern. If match then it will return 1 otherwise 0.

Part 2

To enable our SQL Server database to use the above RegxIsMatch function we need to follow the following procedure.

1.      First build the Library project.

2.      Enable SQL Server to execute CLR code. To do this open SQL Server à New Query Editor.

3.      Select your desired database which you want to use the RegxIsMatch function

4.      Execute the following two command

     sp_configure 'clr enabled', 1

     RECONFIGURE WITH OVERRIDE

Your database is ready to execute CLR code. Now reference the assembly and register the function to your database. To do this executes the following command.

CREATE ASSEMBLY RegxWithSQLServer FROM 'D:\Abu.Zafor.Khairuzzaman\Project\Practice\Service\RegxWithSQLServer\bin\Debug\RegxWithSQLServer.dll'

GO

The path will be the location where you build your library project. Register the function by executing the following command

CREATE Function RegEXIsMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))

RETURNS BIT

EXTERNAL NAME RegxWithSQLServer.[RegxWithSQLServer.RegularExpressions].RegEXIsMatch

GO

 Part 3

You have done all the necessary work. Now what? (J) Lets test your work is it working exactly what you think.

Test 1: I execute the following query and I get result 1

select dbo.RegEXIsMatch('me@mymail.com','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')

Since the input string contains the valid email address then the function return 1.

Test 2: I have a table named ‘Contact’ in my database. The table contains a field named ‘ContactDomain’. Now I want to select all contact from the table whose contact domain is not ‘bordingvista.com’. I use the following query and get the correct result.

select * from Contact

where dbo.RegEXIsMatch(ContactDomain,'^((?!(bordingvista.com)).*)$') != 0

The result before using the query

The result after using the query

That’s all. Please do not use dot net Framework 4.0 because SQL Server CLR does not support Framework 4.0 yet. If there is any problem please let me know via comments.

Thanks with Regards

Shimul Mahmud