regular expressions in transact sql

hi guys,

i need some help tp write code in order to search the string ( regular expressions) in t- sql.

e.g.

when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...

similarly [0-9] means any digit.

the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.

so how do i read the [A-Z] as a range of characters in t-sql?

hi,

In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

as of now SQL Server Like operator only work with following operators

% - Zero or any number of chars

_ - Single Char

[] - Single Char in given range

- Single Char not in given range

if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

For fixed validation (only numbers & only alphabets) i achived the following function,

Create Function dbo.IsMatching(@.Value as varchar(1000), @.Pattern as varchar(100))
returns bit as
Begin
Declare @.Len as int;
Declare @.SearchPattern as varchar(8000);
Declare @.Result as Int;

Select @.Len = Len(@.Value);

While @.Len>0
Begin
Select @.SearchPattern = Isnull(@.SearchPattern,'') + @.Pattern;
Select @.Len = @.Len -1;
End
Select @.Result = Case When @.Value Like @.SearchPattern Then 1 Else 0 End;
Return @.Result;
End

Go

select dbo.IsMatching('SQLServer','[A-Z]') as Result

Result : 1

select dbo.IsMatching('SQL Server','[A-Z]') as Result

Result : 0 --Space on String

select dbo.IsMatching('SQL Server','[A-Z ]') as Result

Result : 1 --Space added on Pattern

select dbo.IsMatching('12453','[1-5]') as Result

Result : 1

select dbo.IsMatching('12463','[1-5]') as Result

Result : 0

|||

thanks mani,

got the [A-Z] and [^a-z] concept.

my other requirements are to match zero or more characters and to match one or more characters.

e.g. T*he should match he, the, tthe, ttttthe.. etc.

and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.

the above operators i have used in VC++,

do they work in t-sql too?

|||If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1|||

Yes.. if you want to find the repeated chars you can use the following condtions....

columnname not like '%aaa%'
and columnname not like '%bbb%'
and columnname not like '%ccc%'
and columnname not like '%ddd%'
and columnname not like '%eee%'
and columnname not like '%fff%'
and columnname not like '%ggg%'
and columnname not like '%hhh%'
and columnname not like '%iiii%'
and columnname not like '%jjj%'
and columnname not like '%kkk%'
and columnname not like '%lll%'
and columnname not like '%mmm%'
and columnname not like '%nnn%'
and columnname not like '%ooo%'
and columnname not like '%ppp%'
and columnname not like '%qqq%'
and columnname not like '%rrr%'
and columnname not like '%sss%'
and columnname not like '%ttt%'
and columnname not like '%uuu%'
and columnname not like '%vvv%'
and columnname not like '%www%'
and columnname not like '%xxx%'
and columnname not like '%yyy%'
and columnname not like '%zzz%'

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1

If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..

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 Server 2005

Hi-

Someone posted on a thread that you can use VB (or C#) to write Regular Expressions for SQL Server 2005. I know how to write RegEx in VB, so can someone point me to a reference which explains how to use them in / with SQL2005? Or, can someone explain how?

Thanks,

-David

OK. At this point I'm pretty sure that this can be accomplished through the common language runtime. Can someone confirm this?

-David

|||

Yes with CLR intergration in SQL Server 2005, you can write CLR Stored procedures in VB or C#. By referring to System.Text.RegularExpressions namespace you can add regular expression support to your stored procs. Here are couple of links to get you started:

http://msdn2.microsoft.com/en-us/library/ms131094(SQL.90).aspx

http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

|||

Thanks for the links. I've been using regex in my VBA project, and MS seems to have done a pretty decent job implementing it. I'm a PERL regex expert from long before which of course helps.

Thanks again.

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