Given that a recent blog of mine lamented the dangers of using xprocs, I thought it would be a good time to show some of the wonderful stuff you can do thanks to SQL Server extensibility features such as xprocs and COM objects. It’s not that xprocs or in-process COM objects are inherently evil -- it’s just that they can be misused and that they can be challenging for the average developer to code properly.
In today’s blog, I’ll show you how to filter T-SQL queries using Regular Expressions. Regular Expressions, you’ll recall, allow sophisticated string searching and matching that goes beyond simple wildcards. T-SQL’s LIKE operator (and PATINDEX() function, which has similar functionality) supports basic wildcards and some simple pattern matching, but has never had anything approaching Regular Expression support. People who’ve written much T-SQL have no doubt encountered situations where they needed string searches that exceeded the meager capabilities of LIKE.
Lurking on any machine that has Windows Scripting Host installed (virtually all machines these days, although scripting can be disabled), is a powerful Regular Expressions facility, the VBScript.RegExp scripting object. You can get to it from any COM client that supports the IDispatch interface. IDispatch, you’ll recall, is COM’s popular late-binding interface – it allows applications to use COM components without knowing anything about them at compile-time. In T-SQL, we get to IDispatch via the sp_OA stored procedures. Via a simple UDF, we can access the RegExp object as though it were part of T-SQL:
USE master
GO
exec sp_configure 'allow updates', 1
go
reconfigure with override
go
DROP function system_function_schema.fn_regex
GO
CREATE FUNCTION
system_function_schema.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
set @match=0
exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OADestroy @obj
return @match
END
GO
exec sp_configure 'allow updates', 0
go
reconfigure with override
go
The UDF above does several interesting things. First, note the use of the system_function_schema pseudo-user to create a system function. A system function is a UDF that’s available from any database context without requiring a fully-qualified name. As I documented in my book The Guru’s Guide to SQL Server Stored Procedures, XML, and HTML, two steps are required to make a UDF a system function: It must be created in the master database with an owner of system_function_schema while allow updates is enabled, and its name must begin with fn_. I’m creating our Regular Expression function as a system function because it’s naturally something that would be useful system-wide. It deserves to be a system function by virtue of its usefulness alone.
Second, note the fact that we call the sp_OA procs directly from our function. If you’ve done much UDF coding, you’re probably aware of the fact that you can’t call regular stored procedures from a UDF. Fortunately for us, although the sp_OA procs are prefixed with "sp_", they’re actually extended procedures, which you can call from a UDF. Equally fortunate is the fact that they aren’t “spec procs”—extended procedures implemented internally by the server. Their entry points are in ODSOLE70.DLL, so they’re callable from a UDF just like any other regular xproc.
The algorithm we use here is embarrassingly simple: We create the object, set some properties, then call the Test method to see whether we have a match. As the code below illustrates, once we’ve wrapped our Regular Expression functionality in a UDF, we can use it to filter a query.
use pubs
go
SELECT au_lname
FROM authors
WHERE fn_regex('G.*',au_lname)<>0
au_lname
Green
Greene
Gringlesby
Ringer
Ringer
Straight
Stringer
As you can see, you don’t need SQLCLR or anything beyond SQL Server 2000 to add this powerful functionality to T-SQL. And, contrary to what I would have guessed, this technique is surprisingly fast – I doubt many users would perceive a performance difference between the fn_regex() function and the built-in LIKE operator. You’ll have to try it yourself to see how it works in your environment, but it was not nearly as slow as I expected a technique built around constructing and tearing down a COM object with each search iteration to be.
So, even though xprocs and COM objects can be abused and can certainly cause problems when not coded properly, on balance, SQL Server, coupled with the objects and facilities lying around on most users’ machines, offers some great extensibility and power with minimal effort.
This technique first appeared in my last book, The Guru’s Guide to SQL Server Architecture and Internals. That book has lots of additional details about Regular Expression use from T-SQL, including how to do so using the .NET Framework’s Regex object rather than VBScript.RegExp. See it for additional Regular Expression search techniques from T-SQL as well as for more info on SQL Server's SQLOLE facility, the component that makes all of this possible.