Extract IP address from a string

Abdul Rahman 21 Reputation points
2022-12-26T10:20:30.013+00:00

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.

{count} votes

Accepted answer
  1. Emilia Simonyan 76 Reputation points
    2022-12-27T06:18:09.253+00:00

    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

    274140-image.png

    No comments

2 additional answers

Sort by: Most helpful
  1. Emilia Simonyan 76 Reputation points
    2022-12-26T18:22:23.757+00:00

    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.

    No comments

  2. Abdul Rahman 21 Reputation points
    2022-12-27T05:00:02.323+00:00

    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

    No comments