CODEWITHSUNDEEP
sqlsql-serverselect
Wingman1487
Wingman1487

Reputation: 112

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.

Upvotes: 2

Views: 3430

Answers (2)

LordBaconPants
LordBaconPants

Reputation: 1414

I would suggest something like this:

SELECT DISTINCT address
FROM your_table
WHERE ISNUMERIC(REPLACE(address,'.',''))=1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 4

Related Questions