Getting the most out of SQL Server: CLR Integration, Part Two

In part one of this article I mentioned that I would be files and documentation for providing regular expression support on SQL Server and that time has now arrived.

Please note that my implementation is very simple, but that was intended. Credits go to Microsoft and MSDN for supplying extensive documentation on the classes and methods involved, especially for the Concatenate Aggregate function source which has nothing to do with regex but I put it there to demonstrate creation of a CLR Aggregate function. One way you could improve the Concatenate Aggregate function would be to add a parameter for specifying a field delimiter (currently a comma).

You can find below two links to the files you will need. If you are interested in the code behind the regex functions you will be creating on your database, please download the project files developed in Visual Studio 2010. If you are only interested in creating the regex functions on your database, please download the binaries.

• Project Files
• Binaries

First of all, understand that I am not giving in-depth instruction on how to write CLR functions in managed code, set up your code editor, compile your code and install it on your database; that is what the project files are for. If you are inquisitive, you may have installed my regex functions using the files I provided already, otherwise, follow these steps:

1. Open install.sql in SQL Server Management Studio
2. Select database from drop-down to install functions on
3. Press CTRL + L SHIFT + M in Query Window and enter template parameters
4. Execute script

Provided you had no errors, the functions will have been created. If you do have problems then comment on this post and I will do my best to help. Also, if you have any questions regarding the development of CLR functions then please feel free to comment on this post or contact me directly.

Something you should be aware of is that if you can install Master Data Services (only available to Enterprise or Business Intelligence users) then you can access regex functionality without writing your own custom CLR functions or installing my regex implementation. You can find details about that in the SQL 2008 R2 regular expressions post on Robert Dyballs blog.