August 7, 2008 at 6:31 pm
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
August 8, 2008 at 2:07 am
In the first case you do pass in apostrophes, so this will not match.
Your next mistake is:
declare @phone char(20)
Note that you create a char(20), which will be 20 characters long (it will be padded with spaces at the end if you do not specify enough character). However, in your regular expression you do check for the end of line character ($).
So in the second invocation if your phone number is less than 20 character, you will not be able to match the regexp.
If you change the line to declare @phone nvarchar(20), then you will get a match as expected.
ps: make sure you remove the spaces from your regexp as well.
Regards,
Andras
August 8, 2008 at 2:17 am
Grrrrrrrrrr, I've just found your other two posts with the same question. One of them was already answered!
http://www.sqlservercentral.com/Forums/Topic548780-145-1.aspx
and
http://www.sqlservercentral.com/Forums/Topic548774-386-1.aspx
Please do not cross post in the future, your answers 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply