SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Regular Expressions in T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

kselvia
Aged Yak Warrior

526 Posts

Posted - 08/06/2004 :  18:47:20  Show Profile  Reply with Quote
Hi Rich,

Select * from mytable where colors not like '%,%'

Please start a new post for this question if you need more info.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."
Go to Top of Page

wayne.twitchell
Starting Member

USA
1 Posts

Posted - 12/15/2004 :  15:56:28  Show Profile  Reply with Quote
This discussion is great. I have one question on a similar topic. UOr vendor places XML contructs into database fields. I am presuming that we could use the reg experssion fucntion to dig them out but since these are XML is there any way to do a select knowing the data is in xml
Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 12/15/2004 :  16:43:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can do that, but SQL Server 2000 has a facility to read and parse XML. Look in Books Online under "OPENXML", it might suit your needs.
Go to Top of Page

jhs2
Starting Member

USA
2 Posts

Posted - 01/03/2006 :  18:58:50  Show Profile  Reply with Quote
yoinky,

I'm in the same boat as cgray, but instead of replaces, I'm trying to do multiple finds from a table of regex. Essentially, I have a table of various URL and domain regex's. I have another table of URLs.

What I'm looking for is a list of URLs that match any of the regex's in the regex table. I can do this just fine with the non-obj functions you provided, but I can't figure out how to do this with the Obj functions.

Any help would be greatly appreciated!

Thanks
Joe.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/04/2006 :  08:05:10  Show Profile  Reply with Quote
Hi jhs2, welcome to SQL Team!

You may want to post a specific question in one of the other forums as "old threads" don't always catch everyone's eye.

Kristen
Go to Top of Page

tgibbs
Starting Member

Canada
1 Posts

Posted - 08/01/2006 :  17:18:08  Show Profile  Visit tgibbs's Homepage  Reply with Quote
I've been trying to get this stuff to work and have managed to do so; however, I have a concern. The dbo.find_regular_exp or the dbo.findregex both seem to take a long time. I have 2200 records (small amount) in a table that have a name and a regular expression of an IP address range. I want to search that table and return the name of IP range that matches an IP Address I'm providing dynamically.

So far, in my testing, it seems to take about 17 seconds per transaction which seems like a long time. In order to search for 300 IP address through my 2200 regular expressions, it's taking nearly 2 hours!!! In my view, this should take seconds or minutes, but not 2 hours.

Any advice on how I can speed this up?

Thanks in advance.

Tyler
Go to Top of Page

Ghayas
Starting Member

Pakistan
2 Posts

Posted - 04/30/2007 :  16:02:45  Show Profile  Reply with Quote
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.


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




Go to Top of Page

Ghayas
Starting Member

Pakistan
2 Posts

Posted - 04/30/2007 :  16:06:01  Show Profile  Reply with Quote
Hello There Sorry buddy i have posted the alreday posted reply unintentionally,
i wanted to know how can i get access to this,means when i go to create this procedure it says that include library name,
if u can tell me what rights i need and how,
thanks in advance.
ghayas
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/30/2007 :  16:38:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Did you read the article?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/01/2007 :  04:01:37  Show Profile  Reply with Quote
"i have posted the alreday posted reply unintentionally"

You can press the little Trash Can icon [next to that post] to delete it, if you like ...
Go to Top of Page

subodhk
Starting Member

India
1 Posts

Posted - 02/08/2008 :  04:03:12  Show Profile  Click to see subodhk's MSN Messenger address  Reply with Quote
Hi I am new to this forum but i have a question related to this topic.
I have a set of regular expression patterns
For Example: '^CVS\-\d\d\d$'. Now while selection I want to use this regular expression for fetching the rows which have value that comply with this regular expression.

I am trying to write a select statement as given below.

SELECT top 10 ticketnum,CSSelection,CSValue FROM customerinfo WHERE CSValue Like '^CVS\-\d\d\d$'. Is this type of statement possible.

Because if theres no way of doing it then I have to fetch the whole recordset and check on row one by one if the CSValue complies to this regular expression.

Any idea will be highly appreciated

subodh konhor
Go to Top of Page

RJSO
Starting Member

1 Posts

Posted - 06/25/2008 :  11:56:20  Show Profile  Reply with Quote
Hello all, first of i would like to thank to the ones who presented this idea it was quite useful to me. However my case was a bit different since i needed to count words from an NTEXT field. I took the liberty to pick some code and do it on my way.

You can see my code bellow:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_regexObjCount]
(
@pattern nvarchar(4000),
@source nvarchar(4000)
)
RETURNS INT AS
BEGIN
DECLARE @hr integer
DECLARE @results bit
DECLARE @objMatch INT
DECLARE @matchcount INT
DECLARE @objRegExp INT

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUT
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @pattern
EXEC @hr= sp_OASetProperty @objRegExp, 'MultiLine', 1
EXEC @hr= sp_OASetProperty @objRegExp, 'IgnoreCase', 1
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', 1
EXEC @hr = sp_OAMethod @objRegExp, 'execute', @objMatch OUT, @source
EXEC @hr = sp_OAGetProperty @objMatch, 'count', @matchcount OUT
IF @hr <> 0
BEGIN
SET @matchcount = NULL
END
RETURN @matchcount
END


This function will return the number of regex matches in a field. Here it goes my usage:

Select [Name], dbo.ufn_regexObjCount('\b\w+?\b',[Name]) from Table1

Till here everything goes as it should, since the query shows the correct number of words for every field. However if i had another condition like this:

Select [Name], dbo.ufn_regexObjCount('\b\w+?\b',[Name]), dbo.ufn_regexObjCount('\b\w+?\b',[Note]) from Table1


It will not show the results for some fields, from the query. Can anyone have enough patience to help me figure out whats happening?
Go to Top of Page

stjo
Starting Member

Norway
3 Posts

Posted - 10/09/2008 :  15:52:51  Show Profile  Reply with Quote
HI,
Can anyone help me. I have created the UF as mention earlier in this forum (regexObj, regexObjReplace and regexObjFind. I have a stored procedure with a cursor that reads about 10.000 rows. For each row I call another stored procedure that uses these UF. And it works great I though.. but. After 128 rows the regex calls fails and returns nothing. I have tried to change the data in the table, with 10.000 different rows but I still get same result. I have tried to move the regex calls to the calling procedure with the cursor, but it still fails. Can anyone help me? This is the code that is failing:

DECLARE @regexWH integer
DECLARE @regexUP integer
SET @regexWH = COMMON.dbo.regexObj( '(^N\-?[0-9]{4})|(^[0-9]{4})|(^NO[0-9]{4})|(^N\-?[0-9]{4})|(^NO\-?[0-9]{4})', 0, 1 )
SET @regexUP = COMMON.dbo.regexObj( '[^0-9]', 1, 1 )
IF (SELECT COMMON.dbo.regexObjFind( @regexWH, @wFelt )) = 1
SET @wFelt = (SELECT COMMON.dbo.regexObjReplace( @regexUP, @wFelt, '' ))

IF I only run the sql below in query analyzer it works:

DECLARE @regexWH integer;
DECLARE @regexUP integer;
SET @regexWH = dbo.regexObj( '(^N\-?[0-9]{4})|(^[0-9]{4})|(^NO[0-9]{4})', 0, 1 )
SET @regexUP = dbo.regexObj( '[^0-9]', 1, 1 )
SELECT innpostnr, dbo.regexObjReplace( @regexUP, innpostnr, '' ) from PREPP01..[Kprp_inn]
WHERE dbo.regexObjFind( @regexWH, innpostnr ) = 1

So.. why does it fails in the procedure with the cursor.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 10/09/2008 :  16:47:16  Show Profile  Reply with Quote
Try moving

SET @regexWH = dbo.regexObj( '(^N\-?[0-9]{4})|(^[0-9]{4})|(^NO[0-9]{4})', 0, 1 )
SET @regexUP = dbo.regexObj( '[^0-9]', 1, 1 )

to the calling procedure and passing @regexWH and @regexUP a arguments to the procedure that executes regexObjFind

The way you are doing it, those are local variables and a new instance of 'VBScript.RegExp' must be created for every row (10,000 times)


--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

stjo
Starting Member

Norway
3 Posts

Posted - 10/09/2008 :  19:26:18  Show Profile  Reply with Quote
Thank you for your trying… I did as you wrothe, buy still it only works on the first 128 rows. Then I moved all the regex code into the calling procedure(with the cursor), so now I only have one procedure. Still it stops after 128 rows..
/Stein
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 10/10/2008 :  12:57:05  Show Profile  Reply with Quote
> Then I moved all the regex code into the calling procedure(with the cursor)

You want to execute these statements only once, outside of the cursor loop

SET @regexWH = dbo.regexObj( '(^N\-?[0-9]{4})|(^[0-9]{4})|(^NO[0-9]{4})', 0, 1 )
SET @regexUP = dbo.regexObj( '[^0-9]', 1, 1 )

If that is what you did, then I'm afraid I don't know why it is failing.


Also, I'm no regex expert, but your expression seems pretty simple. Have you looked at PATINDEX() in SQL Server? There may be a straight forward native way to do what you are doing.


--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

stjo
Starting Member

Norway
3 Posts

Posted - 10/10/2008 :  15:40:31  Show Profile  Reply with Quote
Thank you:) you made my day.. now it works. I am trying to use regex instead of traditional tsql because I have a lot of parsing to do in these procedures.
So, no I am back on track..
/Stein
Go to Top of Page

wasay
Starting Member

Pakistan
1 Posts

Posted - 12/19/2009 :  05:53:24  Show Profile  Reply with Quote
Assalam o Alikum
I am facing a problem i the following senario.
Acctually i ant to use regular expression inside the select statement which will go inside the view. now the DECLARE statement can not be used inside the view so what i did is i define the function call that returns the object and used and used this function all in the statement of regobjFind function call in the place of argument what u passes the reference of com object.

The result is fine but it only returns me 74 rows and not showing me full no of row that the select should return..

Any clue why it is happening?

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.


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;





Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 12/21/2009 :  18:04:53  Show Profile  Reply with Quote
If you put this in the function

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

it needs to be moved outside of it to the calling procedure and pass @regex as a parameter to the function. (Same issue as the last poster - see prior 5 posts)

so instead of

select col1, dbo.my_regex ( firstname, '') from account

you need

DECLARE @regex integer;
SET @regex = dbo.regexObj( '[^a-z]', 1, 1 );
select col1, dbo.my_regex (@regex, firstname, '') from account



--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

Balance
Starting Member

24 Posts

Posted - 02/26/2010 :  17:05:07  Show Profile  Reply with Quote
I created this UDF on my MS SQL 2000, but when I run the code below it always comes back as "Special characters found.", when it shouldn't.

DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- Initialize variables
SET @vchSourceString = 'test@acme.com'

-- Get the length of the string
SET @intLength = LEN(@vchSourceString)

-- Set the completed regular expression (valid email address, per Sun Java class javax.mail.internet.InternetAddress parse() function
SET @vchRegularExpression = '^[a-zA-Z_0-9-''\+~]+(\.[a-zA-Z_0-9-''\+~]+)*@([a-zA-Z_0-9-]+\.)+[a-zA-Z]{2,7}${' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString

IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END

GO

Obviously, "test@acme.com" is a syntactically valid email address. What could be the issue?
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000