A T-SQL Regular Expression Library for SQL Server 2005. Free sou. Introduction With the advent of CLR integration into SQL Server 2005, it has become incredibly easy to extend the power of the T-SQL programming language. Two of the areas that can be improved upon by way of CLR integration are string matching and string manipulation. Background T-SQL has a handful of basic string matching functions (e.g. CHARINDEX, PATINDEX, SOUNDEX) and string matching operators (e.g. =, <>, <, >, LIKE). SQL Server 2005 now allows you to create user defined functions (among other things) using your .NET language of choice. Using the Code General Approach My objective here is to wrap some of the more commonly used static methods of the RegEx class in the .NET Framework into something useable in a T-SQL environment. Interface All four of the functions listed in this article share the same first two parameters: @Input NVARCHAR(MAX) This is the string to be analyzed.
@Pattern NVARCHAR(MAX) This is the regular expression which will be executed against the @Input parameter. Name. Regular Expressions In MS SQL Server using CLR. Filed under In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR Just Fire up Visual Studio and create a new library project (I called it TextFunctions) Add a new Class and Call it Regular Expressions and simply paste in the following Code: using Microsoft.SqlServer.Server; using System.Text.RegularExpressions; using System.Data.SqlTypes; public class RegularExpressions { [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)] public static SqlBoolean RegExMatch(SqlString input, SqlString pattern) { if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match return SqlBoolean.False; //Use the static IsMatch method.
In this example we have two functions: one to Match a Regular expression pattern and one to perform a Replace based on a regular expression pattern. To enable our SQL Server database to make use of the above functions we need to follow the steps below: SQL Server 2005: Regular Expressions Make Pattern Matching And D. SQL Server 2005 Regular Expressions Make Pattern Matching And Data Extraction Easier David Banister Code download available at:Regex2007_02.exe(154 KB) Although T-SQL is extremely powerful for most data processing, it provides little support for text analysis or manipulation.
Attempting to perform any sophisticated text analysis using the built-in string functions results in massively large functions and stored procedures that are difficult to debug and maintain. In fact, regular expressions provide a much more efficient and elegant solution. Regular expressions are not new to SQL. Using the sp_OACreate stored procedure, any OLE automation object that implemented regular expressions could be used, but you had to create a COM object first, then make at least one IDispatch call, then destroy the object. CLR User-Defined Functions CLR user-defined functions are simply static methods (shared functions in Visual Basic) defined within a .NET assembly. Pattern Matching Data Extraction.