Using regular expressions in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using regular expressions in SQL

Author

Topic

procyon4476
Starting Member

4 Posts

Posted - 2010-03-20 : 00:32:08

Not sure if this is possible...

I have a table that contains about 100 regular expressions. I want to join this to another table based on these regular expressions. In other words I want to run a SQL statement like this:

UPDATE myTable
set a.valueToUpdate=b.randomField
from myTable a
join regExTable b
on b.regExValue=a.value <==== but instead do regex match instead of direct match!

This is just pseudocode example, I honestly don't care if it takes a thousand lines of code to accomplish. Any ideas or does this not make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 00:56:15

what do you mean by regex match? can you give an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

procyon4476
Starting Member

4 Posts

Posted - 2010-03-20 : 02:37:56

Regular expression match:

http://en.wikipedia.org/wiki/Regular_expression_examples

So for example, regex table has a value [AEIOU][0-9] in the field REGEXVALUE which means any vowel followed by a number, myTable has a value BCDE9 in the field VALUE, SQL would consider that a match.


Before query or set of queries/functions are run

regExTable:

regExValue      randomField
[AEIOU][0-9]    blahblah

myTable:

value           valueToUpdate
ABCDE9          NULL
BCDF6           NULL
A2ED            NULL

After query or set of queries/functions are run myTable looks like this:

value           valueToUpdate
ABCDE9          blahblah
BCDF6           NULL
A2ED            blahblah

Vowel followed by a number is just an example, my regex table has about 100 regular expressions and will continue to grow over the next year. I know SQL isn't necessarily the best language for this, but we are under the gun to find a way to make this work solely with SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 03:12:49

ok..in that case you need to go 4 dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 06:12:21

Not sure I've got my head around this, but what's wrong with


UPDATE myTable
set a.valueToUpdate=b.randomField
from myTable a
join regExTable b
on a.value LIKE b.regExValue

other than LIKE only supports a subset of RegEx (if you want the full RegEx you'll need to implement an add-on to SQL Server - in SQL2000 that will be a COM object <Yuck!>, in SQL2005/SQL2008 I think you can use a CLR for that (maybe there are other, better, routes in SQL2005/SQL2008?)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-22 : 06:38:24

As kristen said you can use LIKE for the example you posted (a vowel followed by a number surrounded by any string would be)


WHERE
    foo LIKE '%[aeiouAEIOU][0123456789]%'

You *can* included external calls to a regex function but it's generally really really slow. I've only ever had to do it once and that was a long time ago. This is the function I used:


CREATE FUNCTION dbo.fn_regex(
	@pattern VARCHAR(255)
	, @matchstring TEXT
	)
	RETURNS INT

AS BEGIN
	DECLARE @obj INT                   SET @obj = -1
	DECLARE @res INT                   SET @res = -1
	DECLARE @match BIT                 SET @match = 0

	-- Make the @res Object.
	EXEC @res = sp_OACreate 'VBScript.RegExp', @obj OUT
	IF (@res <> 0) RETURN -1

	-- Assign the Pattern to it.
	EXEC @res = sp_OASetProperty @obj, 'Pattern', @pattern
	IF (@res <> 0) RETURN -2

	-- Set to ignore Case
	EXEC @res = sp_OASetProperty @obj, 'IgnoreCase', 1
	IF (@res <> 0) RETURN -3

	-- Execute the regular expression
	EXEC @res = sp_OAMethod @obj, 'Test',@match OUT, @matchstring
	IF (@res <> 0) RETURN -4

	-- Cleanup the object
	EXEC @res = sp_OADestroy @obj

	-- Return the results
	RETURN @match
END
GO

That was away back in 2000. Performance was really, really terrible but it worked.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources