Mark Mintoff My superpower is common sense

2May/120

Adding Regular Expression Support to SQL Server

First and foremost, it is worth noting that SQL Server supports .Net assemblies, allowing us to create methods which can be used from SQL.

.Net assemblies (dll files) can be loaded into the below folder.

With that out of the way, we can get started on creating a .Net assembly. SQL Server does not support regular expressions natively, so I thought it would be useful to add this functionality as my first assembly.

To start, we can create a Class Library Solution (I called mine 'TextFunctions') and add a single class (I called mine 'StringUtility'). The following is the code I wrote for my StringUtility class:

Very simple bit of code, which allows me to check for Regex matches. No big deal. Now we must install this assembly into the database. First we must check to ensure that the database is CLR enabled, so we run:

Next we will create the assembly. Please note that the dll file must be on the same machine as the database is. You might also want to specify the database you will be adding this to. I added a simple "use master;" to my script.

Now we can install the RGX function I created.

Note that the syntax for EXTERNAL NAME is SolutionName.[Namespace.ClassName].MethodName

That is all! You now have a brand new function which executes C# code and returns a boolean value back! Let us try some simple examples:

This function can also be used in WHERE clause conditions as follows:

Enjoy!

VN:F [1.9.22_1171]
Rating: 4.3/5 (3 votes cast)