MS SQL Server 2005 and Regular Expressions
Sunday, November 12th, 2006
http://www.takereal.com/blog/index.php/2006/11/12/15/
1. C# source code (RegExUdf.cs) :
usingMicrosoft.SqlServer.Server;
usingSystem.Text.RegularExpressions;public partial classRegExBase
{
[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticboolRegExMatch(stringpattern, stringmatchString)
{
Regex tmp = newRegex(pattern.TrimEnd(null));
returntmp.Match(matchString.TrimEnd(null)).Success;
}
[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticintRegExMatchesCount(stringpattern, stringmatchString)
{
Regex tmp = newRegex(pattern.TrimEnd(null));
returntmp.Matches(matchString.TrimEnd(null)).Count;
}
[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticstringRegExReplace(stringpattern, stringinputString, stringreplaceString)
{
Regex tmp = newRegex(pattern.TrimEnd(null));
returntmp.Replace(inputString.TrimEnd(null), replaceString.TrimEnd(null));
}
};
2. Let' compile source code:
csc /t:library RegExUdf.cs
In result you have "RegExUdf.dll". I put it into "C:\".
3. Let's go to MS SQL Server 2005 and run query (RegExUdf.sql) :
USE AdventureWorks
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
ALTER DATABASE AdventureWorks
SET TRUSTWORTHY ON;
CREATE ASSEMBLY RegExUdf
FROM 'c:\RegExUdf.dll' - Pease, UPDATE this path !
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION RegExMatch(@pattern nvarchar(255), @str nvarchar(255))
RETURNS bit
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExMatch;
GO
CREATE FUNCTION RegExMatchesCount(@pattern nvarchar(255), @str nvarchar(255))
RETURNS int
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExMatchesCount;
GO
CREATE FUNCTION RegExReplace(@pattern nvarchar(255), @str nvarchar(255), @replace nvarchar(255))
RETURNS nvarchar(255)
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExReplace;
GO
- Example:
SELECT TOP (100) PERCENT ProductID, Name,
dbo.RegExMatchesCount(N'[0-9]', Name) AS MatchesCount,
dbo.RegExReplace(N'[0-9]+', Name, N'X') AS NameReplaced
FROM Production.Product
WHERE (dbo.RegExMatch(N'^.+[0-9].+[0-9]+$', Name) = 1)
ORDER BY ProductID
4. Result:
5. Full source code (RegExUdf.cs RegExUdf.dll RegExUdf.sql) available: RegExUDF.zip
