CLR Function Parameter Passing Issue

Question

CLR Function Parameter Passing Issue

AdPhoenix

Old Hand

Points: 392
More actions
August 7, 2008 at 6:30 pm

#192158

I have created a C# function as below(base code).
public static SqlBoolean fn_RegExMatch(SqlString inpStr,
SqlString regExStr)
{
if (inpStr.IsNull || regExStr.IsNull)
return SqlBoolean.Null;
else
return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value,
RegexOptions.CultureInvariant);
}
Then created corresponding SQL Server function
CREATE FUNCTION dbo.fn_RegExMatch
(@inpstr AS NVARCHAR(MAX), @regexstr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_RegExMatch;
Function Worked Perfect when I pass parameters to it directly
e.g
SELECT C2000_STAGE.dbo.fn_RegExMatch('(973)856-3164','^(?: ($[2-9]\d{2}$ ?|[2-9]\d{2}[-]))?[2-9]\d{2}[-]?\d{4}$')
It returns me output as 1 as expected.
But when I pass IP string as Parameter it reruns 0 ..which is wrong.
declare @phone char(20)
set @phone = '(973)856-2000'
SELECT C2000_STAGE.dbo.fn_RegExMatch(''''+ltrim (rtrim(@phone))+'''','^(?:($[2-9]\d{2}$ ?|[2-9]\d{2}[-]))?[2-9]\d{2}[-]?\d{4}$')
I tried to put quote around Variable ...still dose not work
SELECT C2000_STAGE.dbo.fn_RegExMatch(@phone,'^(?:($[2-9]\ d{2}$ ?|[2-9]\d{2}[-]))?[2-9]\d{2}[-]?\d{4}$')
SELECT C2000_STAGE.dbo.fn_RegExMatch('(973)856-2000','^(?: ($[2-9]\d{2}$ ?|[2-9]\d{2}[-]))?[2-9]\d{2}[-]?\d{4}$')
I get outputs as 0 ,0 ,1 for above 3 select statements.
Any Idea how to fix this issue ?
Help appreciated.It's kind of urgent.
Thanks in advance !
Abhishek

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The problem is that you have @phone declared as a char(20), which means it is going to have trailing spaces for a phone number. Either explicitly cast/convert it to a nvarchar(20) in the function call, or call a .Trim() in the function itself:

SELECT C2000_STAGE.dbo.fn_RegExMatch(cast(@phone as nvarchar(20)),'^(?$[2-9]\ d{2}$ ?|[2-9]\d{2}[-]))?[2-9]\d{2}[-]?\d{4}$')

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

Grrrrrrrrrr, I've just answered the same question on

http://www.sqlservercentral.com/Forums/Topic548775-149-1.aspx

And there is another one at http://www.sqlservercentral.com/Forums/Topic548774-386-1.aspx

Please do not cross post in the future, your answers will be scattered around and difficult to find, and people who will have the same problem will find it difficult to find the relevant answers.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

AdPhoenix Old Hand Points: 392 More actions · Answer 3

AdPhoenix

Old Hand

Points: 392

August 8, 2008 at 12:14 pm

#855436

Thanks!It woked!!