Extract IP address from a string

Question

Hi Team,

I have a column in a table which is acutally SUBJECT line of A TICKET. I would like to extract IP Address from that column. Currently I m extracting this by using VBA code in Excel.

Column A Expected Result
Eg 1: 111.12.123.227 Server is down. | 111.12.123.227
Eg 2: Unable to connect 111.23.112.321 | 111.23.112.321

I need only IP address in another column

Please note IP address can be any where in the string.

Answer 1

Hi,
So, if you have a table named 'tickets', and a column named 'subject', you can use this query

SELECT subject, SUBSTRING(subject, PATINDEX('%..._%', subject), 14) as ip_address
FROM tickets

Answer 2

Hi,

I think you can use a simple formula (MID) either in Excel or VBA to extract the IP address.
Something like this would probably work:
=Mid(A1;FIND("."; A1)-3;14)
or
Mid(Selection, InStr(Selection, ".") - 3, 14)

Alternatively, you could use regex in VBA to find IP address pattern within the text (first, you would need to enable Microsoft VBScript Regular Expressions).

Please, see this example:

Sub test()

Dim RegEx As Object
Dim Match As Object
Dim MyString As String
Dim c As Integer

Set RegEx = New RegExp
RegEx.Pattern = "[\s]?([\d]{3}[.][\d]{2}[.][\d]{3}[.][\d]{3})[\s]?"
RegEx.Global = True

MyString = "111.12.123.227 Server is down"
Set Match = RegEx.Execute(MyString)

For c = 0 To Match.Count
Debug.Print Match(c).Value
Next c

End Sub

Hope this helps.

Answer 3

Hi There,

As I mentioned, I m extracting currently with the help Excel VBA. But Now, I would like to get same results in SQL Column..

Thanks
A