Hi all,
i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?
here is the C# class that I deploy to a db using VS05.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExTest
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern);
if (r1.Match(matchString.TrimEnd(null)).Success)
{
Regex testReg = new Regex(pattern);
Match regMatch = testReg.Match(matchString);
string runnumber = regMatch.ToString().TrimEnd('_');
runnumber = runnumber.TrimStart('_');
return runnumber;
}
else
{
return null;
}
}
};
Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:
use northwind
go
declare @.teststring varchar(50),
@.pattern varchar(10),
@.out varchar(50)
set @.teststring = 'dw_012_34.ext'
set @.pattern = '_\\d{3}_'
set @.out = dbo.RegExMatch(@.pattern, @.teststring )
select @.teststring
select @.pattern
select @.out
Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?
Hi,
without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!
|||i am getting the error while calling
dbo.RegExMatch(@.pattern, @.teststring )
this line in stored procedure,
i have already created the assembly.
still i am getting the error!
|||Which error ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
the scene is this:
after creating an assembly name Reg_ex,
i need to call the method regmatch which is in Reg_ex, in class regtest.
when i say"
CREATE PROCEDURE RegExMatch
AS external name Reg_Ex.RegTest.RegExMatch
error is:
Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1
Could not find Type 'RegTest' in assembly 'Reg_Ex'.
regex work.