udf_GetRegExp

declare @chrTemp varchar(100)
set @chrTemp=convert(varchar(10),@intResultItem)+'").value'
exec @intResult=sp_OAMethod @intRegExp,@chrTemp,@chrResult OUT

create function udf_GetRegExp ()
returns int
as
begin
declare @intRegExp int,@intResult int
exec @intResult=sp_OACreate 'VBScript.RegExp',@intRegExp OUT
return @intRegExp
end

create function udf_getUrls
( @intRegExp int,@chrWhichResult varchar(20),@txtInput text )
returns varchar(100)
as
begin
declare @intResult int,@intMatches int,@intResultCount int,@return varchar(100)
exec @intResult=sp_OASetProperty @intRegExp,[global],true
if @intResult=0
BEGIN
exec @intResult=sp_OASetProperty @intRegExp,[ignorecase],true
if @intResult=0
BEGIN
exec @intResult=sp_OASetProperty @intRegExp,[pattern],'\b(http://)?(\S*\.)+[a-zA-Z]{2,3}(/\S*)*\b'
if @intResult=0
BEGIN
EXEC @intResult=sp_OAMethod @intRegExp,[Execute],@intMatches OUT,@txtInput
if @intResult=0
BEGIN
if @chrWhichResult='count'
BEGIN
EXEC @intResult=sp_OAGetProperty @intMatches,'count',@intResultCount OUT
if @intResult=0
set @return=convert(varchar(100),@intResultCount)
else
set @return='Error getting count'
END
else
BEGIN
EXEC @intResult=sp_OAGetProperty @intMatches,@chrWhichResult,@return OUT
if @intResult<>0
set @return='Error getting result property'
END
END
else
set @return='Error getting result object'
END
else
set @return='Error setting pattern'
END
else
set @return='Error setting ignoreCase'
END
else
set @return='Error setting global'
return @return
end

create table #test( textfield text )
insert #test(textfield)values( 'the url dotd.org/arepark will take you to my website')
insert #test(textfield)values( 'this line does not contain a url')
declare @intRegExp int,@intResult int
set @intRegExp=dbo.udf_GetRegExp()
if @intRegExp=null
print 'Error creating regexp object'
else
select dbo.udf_GetURLS(@intRegExp,'count',textfield) as count,dbo.udf_GetURLS(@intRegExp,'item(0).value',textfield) as url from #test
EXEC @intResult=sp_OADestroy @intRegExp