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

reference : http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html