I found a great post on how to add Regular Expression functions to SQL Server through CLR functions and extended the methods slightly. The functions allow queries such as the one below to be performed. I was concerned with performance initially, but a few tests and I am blown away by the speed. The original post can be found here, http://anastasiosyal.com/archive/2008/07/05/regular-expressions-in-ms-sql-server-using-clr.aspx, and my modified code below.
select dbo.RegExReturnMatch(UserName,
'(\w+\d+)') match from aspnet_Users
where dbo.RegExMatch(UserName,
'(\w+\d+)')
> 0
order
by match
The query above will return usernames from the aspnet_users table that start with words and are followed by numbers (Such as ccook123, but not ccook). Try doing this one with 'like' :)
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;
using System;
public
class
TextFunctions
{
[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. This is more performant than creating a
// new instance of Regex as the static method also caches the last expressions we used.
return
Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase | RegexOptions.Multiline);
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public
static
SqlString RegExReturnMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
return
SqlString.Null;
return
new
SqlString(Regex.Match(input.Value, pattern.Value, RegexOptions.IgnoreCase |
RegexOptions.Multiline).Value);
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public
static
SqlString RegExReturnMatchN(string input, SqlString pattern, SqlInt32 n)
{
if (String.IsNullOrEmpty(input) || pattern.IsNull)
return
SqlString.Null;
Match m = Regex.Match(input, pattern.Value, RegexOptions.IgnoreCase | RegexOptions.Multiline);
if (m.Groups.Count > n)
return (m.Groups[n.Value].Value);
else
return
SqlString.Null;
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public
static
SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return
SqlString.Null;
return
new
SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase | RegexOptions.Multiline));
}
}
/*
*
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME DatabaseMethods.TextFunctions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME DatabaseMethods.TextFunctions.RegExReplace
GO
CREATE Function RegExReturnMatchN(@Input NVARCHAR(MAX),@Pattern NVARCHAR(127), @N int)
RETURNS NVARCHAR(MAX)
EXTERNAL NAME DatabaseMethods.TextFunctions.RegExReturnMatchN
GO
CREATE Function RegExReturnMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS NVARCHAR(512)
EXTERNAL NAME DatabaseMethods.TextFunctions.RegExReturnMatch
GO
*
*/