tsql – search all ipaddresses in a string with the VBScript.RegExp function

 

declare @object int,@property varchar(50),@source varchar(8000)
declare @hr int,@intMatches int,@count int, @return varchar(15)
declare @pattern varchar(255),
 @i int

set @source=’Oct 20 2007 02:47:41: %PIX-1-106021: Deny ICMP reverse path check from 212.166.109.18 to 202.122.19.20 on interface outside’
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,@source
EXEC @hr=sp_OAGetProperty @intMatches,[count],@Count OUT

SET @i=0
WHILE @i<@Count BEGIN
    SET @property=’item(‘ + convert(varchar(10),@i) + ‘).value’
    EXEC @hr=sp_OAGetProperty @intMatches, @property,@return OUT
    PRINT @return
    SET @i=@i+1
END

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s