Thursday, March 17, 2011

MSSQL - Regular Expression with .NET assembly

To do a regular expression on MS SQL, you need to install a custom assembly that executes a regular expression. I've found an article by Roman Khramtsov / Major League (infoatis@gmail.com) that already provides this piece of code. I would love to post that link, but too bad wasn't able to save it, that's why I have to paste the code here.

Just follow these simple steps:
1. Create a new project on .NET (class project)

#region © Copyright 2009, Roman Khramtsov / Major League - SqlRegularExpressions
// SqlRegularExpressions, version 2
//
// © Copyright 2009, Roman Khramtsov / Major League. Contact for business offers: infoatis@gmail.com
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without modification,
// are permitted provided that the following conditions are met:
//
// * Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
// * Redistributions in binary form must reproduce the above copyright notice,
// this list of conditions and the following disclaimer in the documentation
// and/or other materials provided with the distribution.
// * Neither the name of Rudy.net, XmlHelper, nor the names of its contributors
// may be used to endorse or promote products derived from this software
// without specific prior written permission.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
// AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
// ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
// FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
// (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
// LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
// ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
// (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
// EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#endregion

using System; //String
using System.Data.SqlTypes; //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server; //SqlFunctionAttribute


public partial class UserDefinedFunctions
{
///
/// Searches the input string for an occurrence of the regular expression supplied
/// in a pattern parameter with matching options supplied in an options parameter.
///

/// The string to be tested for a match.
/// The regular expression pattern to match.
/// A bitwise OR combination of RegexOption enumeration values.
/// true - if inputted string matches to pattern, else - false
/// Regular expression parsing error.
[SqlFunction(Name="RegexMatch", IsDeterministic=true, IsPrecise=true)]
public static SqlBoolean RegexMatch(SqlString input, SqlString pattern, SqlInt32 options)
{
if (input.IsNull)
{
return SqlBoolean.Null;
}
if (pattern.IsNull)
{
pattern = String.Empty; //""
}
if (options.IsNull)
{
options = 0; //RegexOptions.None
}

try
{
Match match = Regex.Match((string)input, (string)pattern, (RegexOptions)(int)options);
if (match.Value != String.Empty)
{
return SqlBoolean.True;
}
}
catch
{
throw;
}

return SqlBoolean.False;
}

///
/// Searches the input string for an occurrence of the regular expression supplied
/// in a pattern parameter with matching options supplied in an options parameter.
///

/// The string to be tested for a match.
/// The regular expression pattern to match.
/// A bitwise OR combination of RegexOption enumeration values.
/// A regular expression string
/// Regular expression parsing error.
[SqlFunction(Name = "RegexStrMatch", IsDeterministic = true, IsPrecise = true)]
public static SqlString RegexStrMatch(SqlString input, SqlString pattern, SqlInt32 options)
{
if (input.IsNull)
{
return SqlString.Null;
}
if (pattern.IsNull)
{
pattern = String.Empty; //""
}
if (options.IsNull)
{
options = 0; //RegexOptions.None
}

try
{
Match match = Regex.Match((string)input, (string)pattern, (RegexOptions)(int)options);
if (match.Value != String.Empty)
{
return new SqlString(match.Value);
}
}
catch
{
throw;
}

return SqlString.Null;
}
};

2. Compile the project.
3. Install the assembly by executing this sql statement on your MSSQL Server
CREATE ASSEMBLY SqlRegularExpressions
FROM 'C:\MSSQL Assemblies\SqlRegularExpressions.dll'
GO
(or you can install through the SQL Server Management Studio.
4. Create a function that will execute the assembly
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](4000), @pattern [nvarchar](4000), @options [int])
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlRegularExpressions].[UserDefinedFunctions].[RegexMatch]
GO
5. And that's it! You can test your regular expression with the following syntax
SELECT * from tblCountry WHERE [dbo].[RegexMatch](countryname,'Phil.*',1) = 1
6. If you are receiving an error message such as "enable .NET blah blah blah".. execute this script
sp_configure 'clr enabled', 1
go
reconfigure
go

No comments:

Post a Comment