Regular Expressions in SQL 2005 using C#

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'.