Regular Expressions in T-SQL

re: Regular Expressions in T-SQL

YeYan — Wed, 11 May 2005 14:58:00 GMT

RePost : http://www.yeyan.cn/Database/RegularExpressionsTSQL.aspx

Regular Expressions in SQL Server

Adam Machanic — Wed, 29 Jun 2005 16:44:27 GMT

Can you use regular expressions in a WHERE clause?
Ken Henderson shows us how, in this recent blog...

re: Regular Expressions in T-SQL

Clifford Dibble — Sat, 16 Jul 2005 06:25:07 GMT

This code will not run on Yukon.

Period.

1) 'allow updates' is a NOP since direct catalog updates are no longer allowed.
2) There is no more system_function_schema. It was never documented anyway.

It is very unwise to use undoc'ed internal APIs. Don't expect code like this to port to Yukon.

cdibble@microsoft.com
SQL Engine PM

re: Regular Expressions in T-SQL

Andriy Khwyshchun — Tue, 09 Aug 2005 17:06:08 GMT

I tried it in SQL 2k an 2005. 2k - succeeded, 2005 - got message:
Msg 15501, Level 16, State 1, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

re: Regular Expressions in T-SQL

David Keaveny — Mon, 22 Aug 2005 13:14:58 GMT

Great article - this sort of functionality is very useful; a lot of the time, LIKE's selection of pattern matching just doesn't do what you need. I've already used it to identify records with garbage data in them.

However, this approach doesn't scale too happily, as a new COM object is created per row. I found another article that builds on this idea, but reduces the number of object references that need to be created: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205

Working against my development database with 250K, the original query took almost 7 minutes; the revised query took less than 2 minutes, so it's a worthwhile performance improvement.

re: Regular Expressions in T-SQL

Andrew Novick — Sat, 17 Sep 2005 22:45:19 GMT

In SQL 2005 this can be done easily with the SQL-CLR. I use a regular expression UDF as an example in a newsletter. http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N13-dot-net-clr-in-sql-server.htm
I'm pretty sure the regular expression version is going to be faster than creating a com object.

re: Regular Expressions in T-SQL

David Mumladze — Tue, 11 Jul 2006 18:00:56 GMT

This way you don't have to create COM object multiple times...

set quoted_identifier on
go
set ansi_nulls on
go

create function Regex(@rx int, @matchstring varchar(150)) returns int
as
begin
declare @match bit

-- execute "Test(...)" method
exec sp_OAMethod @rx, 'Test',@match out, @matchstring

-- this value is greater than zero upon succesfull match
return @match
end
go

create proc RegexTest
as
begin
set nocount on

declare
@rx int,
@hr int,
@pattern varchar(75)

set @pattern = 'DAV.*'

-- create Regex object and set properties
exec @hr=sp_OACreate 'VBScript.RegExp', @rx OUT
exec @hr=sp_OASetProperty @rx, 'Pattern', @pattern
exec @hr=sp_OASetProperty @rx, 'IgnoreCase', 1

-- execute regular expression matching pattern using Regex UDF
select * from tableName where Regex(@rx, columnName)>0

-- alternative select (much more inefficient than Regex)
-- select * from tableName where columnName like 'DAV%'

-- always call destroy object
exec @hr=sp_OADestroy @rx

-- test
-- exec RegexTest

end

go
set quoted_identifier on
go
set ansi_nulls on
go

re: Regular Expressions in T-SQL

Dan Mabee — Tue, 25 Jul 2006 02:58:02 GMT

we've written similar code. especially important is the bi David Mumladze posted about factoring out the com create/destroy code if performance on medium to large tables is expected.

something else worth noting is that with this approach indexes are useless. since SQL doesn't know about the implementation of the function, it's hands are pretty tied and it has to hand over all values resulting in a table scan every time (unless some other criteria helps knock down the record count 1st.)

we generally recommend to our customers that they use features like like as much as possible and only use regex when nothing else will do.

great blog, btw. love the bits about xp procs. it explains a great deal about some behavior we've seen. we've got a bunch of xp procs that work really well, but i'd still love to get the chance to convert them to clr flavor.

re: Regular Expressions in T-SQL

uhe2jsx@hotmail.com — Thu, 24 Aug 2006 10:21:00 GMT

funny ringtones

re: Regular Expressions in T-SQL

mike — Sun, 01 Oct 2006 02:56:02 GMT

THANKS KEN!! finally regex on sql server that I was actually able to make work!!!

re: Regular Expressions in T-SQL

Anderson Fortaleza — Tue, 06 Feb 2007 23:03:02 GMT

Fantastic !! Thank you so much !

re: Regular Expressions in T-SQL

Frankus — Tue, 31 Jul 2007 20:40:08 GMT

I need help in other sentence. I want know how many if five or more letters be repetead in a word.

You can help me in this??

How to optimize search on an indexed field | keyongtech

How to optimize search on an indexed field | keyongtech — Thu, 22 Jan 2009 09:28:05 GMT

PingBack from http://www.keyongtech.com/2092955-how-to-optimize-search-on