Wingman1487 Wingman1487 - 1 year ago 97
SQL Question

Select IP address from MSSQL column

I'm getting an output from one of our scripts that sends over IP address mixed with Host-names, so what I'm trying to do is setup a query that will differentiate the IPs from the Host-names so I can try to resolve the using Powershell (or something else) later. My question is, how can I select only the IP addresses from the row when my data looks something like below (just making stuff up to get the point out there).

What I would want to get back would be just

Edit I've seen some things suggesting PARSENAME, however I'm not sure how that would work with the FQDN being in there.

Answer Source

Based on the reference to parsename(), I am assuming SQL Server:

where ip like '%.%.%.%' and        -- has three periods
      ip not like '%.%.%.%.%' and  -- does not have four periods
      ip not like '%[^0-9.]%'      -- has only numbers and periods

In almost any other database, you can use a regular expression.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download