tsql - search all ip address from table columns

 

declare @object int,
@return varchar(15),
@pattern varchar(255),
@hr int,
@source varchar(8000),
@intMatches int,
@Count int

set @pattern='(\d+\.\d+\.\d+\.\d+)'
--set @property='item(0).value'

EXEC @hr=sp_OACreate 'VBScript.RegExp',@object OUT
EXEC @hr=sp_OASetProperty @object,[global],true
EXEC @hr=sp_OASetProperty @object,[ignorecase],true
EXEC @hr=sp_OASetProperty @object,[pattern],@pattern

--EXEC @hr=sp_OAMethod @object,[Execute],@intMatches OUT,'1.2.3.5 3,4,5,6, 2.3.4.5' --@source
--EXEC @hr=sp_OAGetProperty @intMatches,[count],@return OUT
--select dbo.udf_RegExp_Execute(@object,'item(1).value','1.2.3.5 3,4,5,6, 2.3.4.5')

 

-- example
select top 100
dbo.udf_RegExp_Execute(@object,'count ',messagetext), 
dbo.udf_RegExp_Execute(@object,'item(0).value',messagetext),
dbo.udf_RegExp_Execute(@object,'item(1).value',messagetext)

from table1

EXEC @hr=sp_OADestroy @object

 

-- helper function

create function udf_RegExp_Execute(@object int,@property varchar(100) ,@source text )
returns varchar(100) as
begin
declare @return varchar(100), @hr int, @i int
EXEC @hr=sp_OAMethod @object,[Execute],@i OUT,@source
EXEC @hr=sp_OAGetProperty @i,@property,@return OUT
return @return
END