Siusic Dot Com

I was looking for a way to find the first occurrence of 4 digit

year in a column containing free from text. The first thing I think of was to use regular expression. I did some research and came up with the following function. Once the function is created it can be used like this,
select dbo.fn_regex (’[1-2][0,9][0-9][0-9]’, ‘free form text’),
to return the first matching substring. This function can be use on SQL 2000 and SQL 2005. However in SQL 2005 it is better to use CLR function. You can refer to the following link for further detail.

T-SQL regular expression in SQL 2000
T-SQL regular expression in SQL 2005

create FUNCTION
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS varchar(100)
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
declare @objMatch int
declare @matchpart varchar(100)
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_OAMethod @obj, ‘execute’, @objMatch OUT, @matchstring
IF (@res <> 0)
RETURN NULL
ELSE
EXEC @res= sp_OAGetProperty @objmatch, ‘item(0).value’, @matchpart OUT

exec @res=sp_OADestroy @obj

–return @match
return @matchpart
END

Related Posts:

VB Script to Rename All Files in a Folder

Chinese Translation on Google.cn is Getting Amazing

How to Test Numeric Value in XSL Style Sheet

Hard to Kill Malware: Wintems.exe, Hldrrr.exe and Random Number.exe

How to Trouble Shoot SQL Server Job Error 8198 - Unable to Determine if the Owner of Job Has Server Access