Extract IP address from a string

Abdul Rahman 21 Reputation points

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: Server is down. |
Eg 2: Unable to connect |

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

    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


    No comments

2 additional answers

Sort by: Most helpful
  1. Emilia Simonyan 76 Reputation points


    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)
    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 = " 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

    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..


    No comments