Passing Parameter to Function

• AdPhoenix

  Old Hand

  Points: 392

  More actions

  August 7, 2008 at 6:39 pm

  #93699

  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

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 7, 2008 at 8:10 pm

  #854877

  Please don't put in duplicate posts as folks will waste their time looking at both.

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

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question