Why does my CLR RegEx function always return 0?

Question

Why does my CLR RegEx function always return 0?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
April 21, 2009 at 9:23 am

#203197

I put together a CLR RegexMatch function based on code found online in a couple of sources. The code seems to make sense to me but when I call the function: Select dbo.RegexMatch('a','\d') it returns 0. When I call it with ('1','\d') it returns 0. Since "\d" is a digit shouldn't one of those return a 1?
Here's the code:
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline;
[SqlFunction(IsDeterministic=true, IsPrecise=true)]
public static SqlBoolean RegexMatch(SqlChars input, SqlString pattern)
{
Regex regex = new Regex(pattern.Value, Options);
return regex.Match(input.ToString()).Success;
}
};
It builds successfully and deploys to my local SQL Server successfully.
Any help would be greatly appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 1

I found a slightly different variant that works by changing the function parameters from SQLBoolean and SQLString to bool and string:

using System.Data.SqlTypes;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline;

[SqlFunction(IsDeterministic=true, IsPrecise=true)]

public static bool RegexMatch(string input, string pattern)

{

Regex regex = new Regex(pattern, Options);

return regex.Match(input.TrimEnd(null)).Success;

}

};

So I have something that works but if someone could explain to me why the first one didn't even though it is almost a direct copy out of an MSDN article...

daniel.janz Valued Member Points: 61 More actions · Answer 2

Haven't tried your method but I just wanted to mention that you can shorten your code by using the static Regex.Match method.

MSDN: http://msdn.microsoft.com/en-us/library/bk1x0726.aspx

ERP Software & IT-Beratung München

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

Thanks. I finally convinced our SQL guys to write a comprehensive RegEx CLR module since they were never happy with my code.