Regular expressions in Sql Server?

Hi,

I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:

((\w)*\|)*$KEY(\|(\w)*)*

as would be defined in Perl (btw, the "|" is a pipe, not an L :))

$KEY would be subsituted by some text I'm querying for.

I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:

select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'

but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.

Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.

I believe "\|" in perl is how you specify a literal "|".

Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.

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

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