Wingman1487 Wingman1487 - 22 days ago 7
SQL Question

Select IP address from SQL Server 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).

host123.na.root.net
host123.na.root.net
123.123.123.123
11.22.123.23
host789
host789
123.123.123.123
111.23.23.132


What I would want to get back would be just

11.22.123.23
123.123.123.123
111.23.23.132


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

Answer

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.