Thursday, August 7, 2008

Adding Regular Expressions (Regex) to SQL Server 2005

It is very easy to add Regular Expressions or other custom functionality to SQL Server 2005 because it support CLR. This means all you have to do it create a custom Assembly, mark it up with some attributes (similar to the way you do web services), and click the deploy button, and that is it. Microsoft really does make it easy using Visual Studio 2005 (and 2008 I assume). All you have to do it create a SQL Server Project. You can use VB.NET or C#, just pick the appropriate SQL Server Project. It pretty much walks you through creating the project. After it is done, you will need to right-click the project | Add | User-Defined Function... Give it whatever name you want. It gives you a simple stub. Just build and deploy. It deploys the assembly to the database it helps you with initially, and makes User-Defined functions (that call the assembly). You can then call your function like any other User-Defined function. The name and parameters show up in the User-Defined functions section under Database | Programmability | Functions | Scalar-valued Functions. It was also recommended by someone (see references) to in execute the following SQL (I only did it the first time I deployed) to enable CLR and install required support. sp_configure 'clr enabled',1 reconfigure There is one VERY important thing you need add to any method you want to be able to access from SQL. You must add the attribute [SqlFunction]. The method must also be public and static I believe. The parameters and return value have to be SQL types like: SqlChars, SqlString, SqlInt32, etc. You can use standard C# and VB.NET types everywhere within your method, but the parameters and return value MUST be SQL types. Below is my implementation (or at least part what I wrote and part an adaptation of parts from what other people wrote... see references) of three key Regular Expression methods I think are very useful.
  • RegexMatch - returns 1 if pattern can be found in input, else 0
  • RegexReplace - replaces all matches in input with a specified string
  • RegexSelectOne - returns the first, second, third, etc match that can be found in the input
  • RegexSelectAll - returns all matches delimited by separator that can be found in the input
Examples of how to use them in SQL:
  • select dbo.RegexMatch( N'123-45-6749', N'^\d{3}-\d{2}-\d{4} Returns 1 in this case since the phone number pattern is matched
  • select dbo.RegExReplace('Remove1All3Letters7','[a-zA-Z]','') Returns 137 since all alpha characters where replaced with no character
  • select dbo.RegexSelectOne('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', 0) Returns 123-45-6789 since first match was specifed. If last parameter was 1 then the second match (222-33-4444) would be returned.
  • select dbo.RegexSelectAll('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', '|') Returns 123-45-6749|222-33-4444
The actual implementation of this is nothing special other than the conversion of SQL Types. The complete source code (no project since it is so specific to your environment) to the implementation is available for download here.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;

public partial class UserDefinedFunctions
{

    public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;

    [SqlFunction]
    public static SqlBoolean RegexMatch(SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex(pattern.Value, Options);
        return regex.IsMatch(new string(input.Value));
    }

    [SqlFunction]
    public static SqlString RegexReplace(SqlString expression, SqlString pattern, SqlString replace)
    {
        if (expression.IsNull || pattern.IsNull || replace.IsNull)
            return SqlString.Null;

        Regex r = new Regex(pattern.ToString());
        return new SqlString(r.Replace(expression.ToString(), replace.ToString()));
    }

    // returns the matching string. Results are separated by 3rd parameter
    [SqlFunction]
    public static SqlString RegexSelectAll(SqlChars input, SqlString pattern, SqlString matchDelimiter)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));

        StringBuilder sb = new StringBuilder();
        while (results.Success)
        {
            sb.Append(results.Value);

            results = results.NextMatch();

            // separate the results with newline|newline
            if (results.Success)
            {
                sb.Append(matchDelimiter.Value);
            }
        }

        return new SqlString(sb.ToString());

    }

    // returns the matching string
    // matchIndex is the zero-based index of the results. 0 for the 1st match, 1, for 2nd match, etc
    [SqlFunction]
    public static SqlString RegexSelectOne(SqlChars input, SqlString pattern, SqlInt32 matchIndex)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));

        string resultStr = "";
        int index = 0;

        while (results.Success)
        {
            if (index == matchIndex)
            {
                resultStr = results.Value.ToString();
            }

            results = results.NextMatch();
            index++;

        }

        return new SqlString(resultStr);

    }

};

What I used to get started. http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx How to optimize regex calls: http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx More Information: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx I found this after writing this, but it explains other details I did not. http://www.codeproject.com/KB/string/SqlRegEx.aspx?display=PrintAll

39 comments:

Adam said...

Just geeks, I'm hoping for some help!!

Everything I've read about CLR says it's super easy. I've downloaded the C# code from Microsoft (http://msdn.microsoft.com/en-us/magazine/cc163473.aspx), but my Visual Studio 2005 doesn't have the option to create a SQL Server Project or even the "Languages" drop down on the left.

Do I need to install C# in VS somehow?!

Thanks,
Adam

Brent V said...

Adam,

Did you install Visual Studio 2005 explicitly? When you do you select the languages you want to have installed. By default C# is one of them. What version of Visual Studio 2005 are you running? You can check it by choosing About Microsoft Visual Studio under the Help menu (which is one of the menus in Visual Studio 2005). You should have something like Professional Edition or maybe you downloaded the FREE Express version.

My guess is that you still need to install a full version of Visual Studio 2005. I suspect you are probably looking at the Visual Studio 2005 that gets installed with MS SQL Server 2005. This is NOT what you need. It is really just a stripped down version of VS2005, and does not have support for C#, websites, etc that the full versions have.

You can if you have the MS SQL Server version of VS2005 installed by doing the same as I described above, but you will see that the name is Microsoft Visual Studio 2005, but it will not say anything else like Express or Professional Edition. You will also only see 3 products installed in that same window. You will likely see, SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services. If C# was included in the installation, you would see Microsoft Visual C# 2005 in this same list.

Assuming I am correct, I recommend VS2005 Professional Edition since that is what I am using and I know it works. You can try the Visual C# 2008 Express Edition, but I have not tried it and suspect it does not support the SQL Server project type. You can download it here (http://www.microsoft.com/eXPress/download/). In theory, you could do this from any Library project I would imagine, but deploying manually is not for the faint of heart and I would not recommend it for someone not familiar with how to do so unless you find some docs on it. Visual Studio Profession 2005 does the deployment with a very nice user interface.

Let me know how it goes.

I hope you find this useful.

Brent

Anonymous said...

You don't need Studio to register. Use the Express version, create a Dll project called RegExForSQL. Add a class file. Replace the class with the UserDefinedFunctions above.
Compile the project

Copy the dll to an installation location. For instance c:\RegExForSQL

Use the following to register the DLL in you SQL database update the bold areas before running.



EXEC sp_configure 'clr enabled', '1'

GO

reconfigure
Use [DatabaseName]

GO
CREATE ASSEMBLY asmRexExp from 'C:\RegExForSQL\RegExForSQL.dll' WITH
PERMISSION_SET = SAFE



GO

--RegexMatch(SqlChars input, SqlString pattern) CREATE FUNCTION
USE [ScratchPad]
GO

/****** Object: UserDefinedFunction [dbo].[RegexMatch] Script Date:
11/03/2009 18:34:40 ******/
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern
[nvarchar](max))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [asmRexExp].[RegExForSQL.UserDefinedFunctions].[RegexMatch]
GO


GO




/* Returns a comma separated string of found objects
* Example usage SELECT [Message], dbo.RegexReplace(
'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|
[01]?[0-9][0-9]?)\b', [Message], 'new text') as [NewText] from tblSample
* C# function --SqlString RegexReplace(SqlString expression, SqlString
pattern, SqlString replace)
*
*/
USE [ScratchPad]
GO

/****** Object: UserDefinedFunction [dbo].[RegexReplace] Script Date:
11/03/2009 18:34:20 ******/
CREATE FUNCTION [dbo].[RegexReplace](@expression [nvarchar](max), @pattern
[nvarchar](max), @replace [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [asmRexExp].[RegExForSQL.UserDefinedFunctions].[RegexReplace]
GO


GO



/* Returns a comma separated string of found objects
* Example usage SELECT [Message], dbo.RegexSelectAll([Message],
'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[
01]?[0-9][0-9]?)\b', ',') as [IPAddress] from tblSample
* C# function --SqlString RegexSelectAll(SqlChars input, SqlString pattern,
SqlString matchDelimiter)
*
*/

GO

/****** Object: UserDefinedFunction [dbo].[RegexSelectAll] Script Date:
11/03/2009 18:34:00 ******/
CREATE FUNCTION [dbo].[RegexSelectAll](@input [nvarchar](max), @pattern
[nvarchar](max), @matchDelimiter [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[asmRexExp].[RegExForSQL.UserDefinedFunctions].[RegexSelectAll]
GO


GO




/* Returns finding matchIndex of a zero based index
* Example usage SELECT [Message], dbo.RegexSelectOne([Message],
'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[
01]?[0-9][0-9]?)\b', 0) as [IPAddress] from tblSample
* C# function --SqlString RegexSelectOne(SqlChars input, SqlString pattern,
SqlInt32 matchIndex)
*
*/

GO

/****** Object: UserDefinedFunction [dbo].[RegexSelectOne] Script Date:
11/03/2009 18:33:34 ******/
CREATE FUNCTION [dbo].[RegexSelectOne](@input [nvarchar](max), @pattern
[nvarchar](max), @matchIndex [int])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
[asmRexExp].[RegExForSQL.UserDefinedFunctions].[RegexSelectOne]
GO

Brent V said...

Anonymous,

Thank you very much for sharing that wealth of knowledge. I knew it was possible, but I never had a need to figure out all the code. Good work!

Thank you,

Brent

Daniel said...

Thanks for the informative post and the links posted.

Darrell Robert Parker said...

Great stuff, got me past a roadblock with using regular sql functions to do a complex match.

Turned out that Visual Studio 2008 was also giving me issues with the server explorer, used devenv /setup in Visual studio command prompt to fix that and then your code worked perfectly.

I will be loading up my sql database with a bunch of regular expression functions after this.

Thanks again

Mithun Mithun said...

Thanks for your informative articel .its very useful
dot net training in velachery | dot net training in chennai

Pavithra M said...

It is really a great work and the way in which u r sharing the knowledge is excellent.
Thanks for helping me to understand basic concepts. As a beginner in dot net programming your post help me a lot.Thanks for your informative article.Dot Net training in chennai | Dot Net training institutes in chennai | Dot Net training in velachery


Kingsly David said...

Great post! Thanks for sharing with us.

Angularjs Training in Chennai | Web Designing Training in Chennai

Prasad Gowtham said...

Great blog! Thanks for giving such valuable information, this is unique one. Really admired

Dot Net Training in Chennai

sai said...

You’ve written a really great article here. Your writing style makes this material easy to understand.. I agree with some of the many points you have made. Thank you for this is real thought-provoking content
ANGULARJS
Click here:
Angularjs training in chennai

Click here:
angularjs training in bangalore

Click here:
angularjs training in online

Click here:
angularjs training in Annanagar

john jersy said...

A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts read this.
Click here:
Microsoft azure training in chennai
Click here:
Microsoft azure training in online
Click here:
Microsoft azure training in tambaram
Click here:
Microsoft azure training in chennai
Click here:
Microsoft azure training in annanagar

pooja saravanan said...

Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

Blueprism training in Chennai

Blueprism training in Bangalore

Blueprism training in Pune

Blueprism training in tambaram

Blueprism training in annanagar

Blueprism training in velachery

Blueprism training in marathahalli


AWS Training in chennai

AWS Training in bangalore


gowsalya said...

Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
Devops training in tambaram|Devops training in velachery|Devops training in annanagar|Devops training in sholinganallur

sudha P said...

We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!
java training in chennai | java training in bangalore

java online training | java training in pune

Ram priya said...

This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.
Data Science training in rajaji nagar | Data Science with Python training in chenni
Data Science training in electronic city | Data Science training in USA
Data science training in pune | Data science training in kalyan nagar

Anonymous said...

Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
angularjs-Training in pune

angularjs-Training in chennai

angularjs Training in chennai

angularjs-Training in tambaram

angularjs-Training in sholinganallur

saran latha said...

Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
advanced excel training in bangalore

gowthunan said...

Expected to form you a next to no word to thank you once more with respect to the decent recommendations you've contributed here.
health and safrety courses in chennai

pavithra dass said...

thanks for sharing such a nice info.I hope you will share more information like this. please keep on sharing!
Core Java Training in Chennai
Java Training center in Chennai
Java Certification course in Chennai
German Training in Chennai
german classes chennai
german teaching institutes in chennai

Bhuvana said...

I accept there are numerous more pleasurable open doors ahead for people that took a gander at your site.we are providing ReactJs training in Chennai.
For more details: ReactJs training in Velachery | ReactJs training in chennai

Bhuvana said...

I accept there are numerous more pleasurable open doors ahead for people that took a gander at your site.we are providing ReactJs training in Chennai.
For more details: ReactJs training in Velachery | ReactJs training in chennai

sachin.ogeninfo said...

animal feed bags supplier

aruna ram said...

Great idea! Thank you for your wonderful post and very easily understand to me. Really good work please keeping...
Web Designing Training in Bangalore
Web Development Courses in Bangalore
Web Designing Course in Tnagar
Web Designing Training in Saidapet
Web Designing Course in Omr
Web Designing Training in Omr

click track India said...

Thinking of growing as best packers and movers in Mohali? Just click on click track india, and you are ready for the skyrocket sales.

Packers and movers in Chandigarh

Packers and movers in Mohali

Packers and movers in Noida

Packers and movers in Gurgaon

Packers and movers in Delhi NCR

Packers and movers in Bangalore

ajay prakash said...

Thanks for providing wonderful information with us. Thank you so much.
Aviation Academy in Chennai
Aviation Courses in Chennai
best aviation academy in chennai
aviation training in chennai

REKHA PUNIA said...

If you live in Delhi and looking for a good and reliable vashikaran specialist in Delhi to solve all your life problems, then you are at right place. 
love marriage specialist in delhi

vashikaran specialist in delhi

love vashikaran specialist molvi ji

get love back by vashikaran

black magic specialist in Delhi

husband wife problem solution

lather said...

Goyal packers and movers in Panchkula is highly known for their professional and genuine packing and moving services. We are top leading and certified relocation services providers in Chandigarh deals all over India. To get more information, call us.


Packers and movers in Chandigarh
Packers and movers in Panchkula
Packers and movers in Mohali
Packers and movers in Zirakpur
Packers and movers in Patiala
Packers and movers in Ambala
Packers and movers in Ambala cantt
Packers and movers in Pathankot
Packers and movers in Jalandhar
Packers and movers in Ludhiana

lekha mathan said...

Very good information provided, Thanks a lot for sharing such useful information.
air hostess training in Bangalore
air hostess academy Bangalore
cabin crew course in Bangalore
cabin crew training in Bangalore

Riya Raj said...

The blog is really awesome…. waiting for the new updates…
Angularjs Training institute in Chennai
Angular 2 Training in Chennai
Angularjs Course in Bangalore
Angularjs Training Institute in Bangalore

aruna ram said...

Such a wonderful blog! I got more info to your post. Thank you for your sharing with as. Keep posting...
Blue Prism Training Bangalore
Blue Prism Training in Bangalore
Blue Prism Classes in Bangalore
Blue Prism Training in Annanagar
Blue Prism Course in Annanagar
Blue Prism Training in Chennai Adyar

sathyaramesh said...

Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
Software Training Institutes in Chennai | Software Testing Training Institutes in Chennai
software testing course in coimbatore with placement | best software testing training institute in coimbatore
software training institutes in bangalore | software testing institute in bangalore
software testing madurai | software testing classes in madurai

nanadhini chandran said...

Thanks for your interesting ideas.the information's in this blog is very much useful
for me to improve my knowledge.
Cloud computing courses in Bangalore
Cloud Computing Training in Anna Nagar
Cloud Computing Training in T nagar
Cloud Computing Training in OMR

keerthana said...

Thanks for providing wonderful information with us. Thank you so much.
Airport management courses in chennai
diploma in airport management course in chennai
diploma in airline and airport management in chennai
airlines training chennai

jenifer irene said...

Thanks for providing wonderful information with us. Thank you so much.
airport ground staff training courses in chennai
airport ground staff training in chennai
ground staff training in chennai

cynthia williams said...

Thanks for taking time to share this valuable information admin. Really helpful.
ReactJS Training in Chennai
AngularJS Training in Chennai
AngularJS course in Chennai
RPA Training in Chennai
R Programming Training in Chennai
UiPath Training in Chennai
Data Science Course in Chennai
Machine Learning Training in Chennai

Riya Raj said...

Really great blog... Thanks for your information
Selenium Course in Bangalore
selenium course in coimbatore
Java Training in Bangalore
Python Training in Bangalore
IELTS Coaching in Madurai
IELTS Coaching in Coimbatore

Java Training in Coimbatore

jefrin adams said...

Very nice to read the post
microsot azure certification training in chennai

minakshi said...

Are you trying to move in or out of Jind? or near rohtak Find the most famous, reputed and the very best of all Packers and Movers by simply calling or talking to Airavat Movers and Packers

Packers And Movers in Jind

Packers And Movers in Rohtak

Movers And Packers in Rohtak