quote:

---

Originally posted by kapcreations

can you give an example for all the functions you provided?

---

quote:

---

Originally posted by yoinky

I'm sort of new to MSSQL and I just found this article and was thrilled to find a way to use regular expressions in T-SQL!

However I was more interested in using the regular expression replace functionality. So after a little bit of tweaking to the original UDF and using the Microsoft reference for VBScript.regex I was able to get it to work!

I then read the comments by Blastrix and thought he had a very good point about execution speed because of instantiating SO many COM objects. So I modified the regexFind and regexReplace UDFs as he had pointed out to create a second more efficient set of UDFs that would accept the regular expression object as a parameter.

In a basic test on my laptop I ran 30,000 records through a regexReplace, which took 27 seconds. I ran the same thing with the new regexObjReplace and it took 7 seconds. Its by no means an accurate benchmark of its performance, but it does show how dramatic the difference can be.

I did change the functionality a bit. If an error occurs the COM object is first destroyed, and then a NULL value is returned. I removed the unused parameters @objMatches, @objMatch, and @count.

Well I hope someone finds this code useful. As I said, I am pretty new to this, so if anyone has any improvements for this please let me know!

Here is an example of how to use it, this removes any characters other then a-z from the field 'firstname'.

---

The simple method is:

SELECT dbo.regexReplace( firstname, '[^a-z]', '', 1, 1 ) FROM account;

---

The more efficient method is:

DECLARE @regex integer;
SET @regex = dbo.regexObj( '[^a-z]', 1, 1 );
SELECT dbo.regexObjReplace( @regex, firstname, '' ) FROM account;

---

CREATE FUNCTION dbo.regexObj
(
@regexp varchar(1000),
@globalReplace bit = 0,
@ignoreCase bit = 0
)
RETURNS integer AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer

EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 BEGIN
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @objRegExp
END
GO


CREATE FUNCTION dbo.regexObjFind
(
@objRegExp integer,
@source varchar(5000)
)
RETURNS bit AS
BEGIN
DECLARE @hr integer
DECLARE @results bit

EXECUTE @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @results
END
GO


CREATE FUNCTION dbo.regexObjReplace
(
@objRegExp integer,
@source varchar(5000),
@replace varchar(1000)
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @hr integer
DECLARE @result varchar(5000)

EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @result
END
GO


CREATE FUNCTION dbo.regexFind
(
@source varchar(5000),
@regexp varchar(1000),
@ignoreCase bit = 0
)
RETURNS bit AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @results bit

SET @results = 0

EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @results
END
GO


CREATE FUNCTION dbo.regexReplace
(
@source varchar(5000),
@regexp varchar(1000),
@replace varchar(1000),
@globalReplace bit = 0,
@ignoreCase bit = 0
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @result varchar(5000)

EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @result
END
GO

---

quote:

---

Originally posted by pittsburghsteelersfan

hello...I was happy to se this, and hoped that it would solve my problem...i have a table with about 50,000 replacement regex's that need to be run against 2 fields in a different table...vb.net seems to have a memory issue because running these causes the memory usage to skyrocket to the point where the standalone app crashes. I was hoping to make it work directly in SQL server and I hope someone can help!!! Any help greatly appreciated. Thanks. Patrick.

---

DECLARE	@strTest varchar(8000)

SELECT @strTest = '\\servername\foldername99999\filename.ext'

SELECT	[Position]=PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest),
	[Before]=SUBSTRING(@strTest, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest)-1),
	[After]=SUBSTRING(@strTest, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), LEN(@strTest))