Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IP Address not formatted Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 7:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 02, 2010 9:26 AM
Points: 20, Visits: 58
Hello,

I have a column that has IP addresses and some of the IP addresses are not complete. For example, there may be IP address like 8.10.168. OR 7.123.456.AVC, etc. So, in this case I would like to flag those ID's that has correct IP addresses or incorrect IP addresses. Thank you.
Post #733454
Posted Thursday, June 11, 2009 8:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 5:55 PM
Points: 957, Visits: 1,027
Okay. So what's the problem with doing that?
Post #733457
Posted Thursday, June 11, 2009 8:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 02, 2010 9:26 AM
Points: 20, Visits: 58
Thank you Bruce.
So, that means I would like help with T-Sql that will show me ID's with correct and incorrect IP Addresses.

For Example: tblIP

ID IPAdd
101 8.143.46.
102 8.243.6.AC
103 192.168.1.1
104 192.BC.168.1.1
105 192.212.456.21

So, I'd like help with something like this below:

SELECT ID
FROM IPAdd
WHERE (....code goes here that shows either the correct IPs or Incorrect IPs).

Thank you.
Post #733468
Posted Thursday, June 11, 2009 8:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 5:55 PM
Points: 957, Visits: 1,027
Well, thinking about it, it's actually a little tricky. The best solution would be something with full regular expression handling to cope with it.

However, you could use a pattern like:

WHERE IPAdr not like '[0-9]%.[0-9]%.[0-9]%.[0-9]%' or IPAdr like '%[^0-9.]%'

That will at least get you the IP addresses that contain characters that aren't digits or full stops and aren't in the form 'A.B.C.D'.

It won't pick up something like: '01234567.89101112.13141516.17181920'.

The only way I can think of is to pull apart the string: split it on the full stop (period) character, and check that the value of the four parts are between 0 and 255 (do a search for string splitting in SQL Server -- there are several good approaches that will do it.)

Except... what about IP6 addresses? This will only work for IP4 addresses.

So as I said, it's a little bit tricky. Where are you getting the IP addresses from initially?
Post #733471
Posted Thursday, June 11, 2009 9:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 5:55 PM
Points: 957, Visits: 1,027
Here's some example code to give you an idea:

declare @IPAddress table(AddressID int not null primary key, IPAddress varchar(15) not null);

insert into @IPAddress
select 101, '8.143.46.' union all
select 102, '8.243.6.AC' union all
select 103, '192.168.1.1' union all
select 104, '192.BC.168.1.1' union all
select 105, '192.212.456.21' union all
select 106, '1.2.3.4.5.6'
;

with
MarkedAddress as (
select AddressID,
IPAddress,
0 as Marker0,
charindex('.', IPAddress, 1) as Marker1,
charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) as Marker2,
charindex('.', IPAddress, charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) + 1) as Marker3,
len(IPAddress) + 1 as Marker4
from @IPAddress
),
PartionedAddress as (
select AddressID,
IPAddress,
substring(IPAddress, Marker0 + 1, Marker1 - Marker0 - 1) as Part1,
substring(IPAddress, Marker1 + 1, Marker2 - Marker1 - 1) as Part2,
substring(IPAddress, Marker2 + 1, Marker3 - Marker2 - 1) as Part3,
substring(IPAddress, Marker3 + 1, Marker4 - Marker3 - 1) as Part4
from MarkedAddress
)
select *,
case
when IPAddress like '%[^0-9.]%' then 0
when IPAddress like '%.%.%.%.%' then 0
when IPAddress not like '[0-9]%.[0-9]%.[0-9]%.[0-9]%' then 0
when cast(Part1 as bigint) > 255 then 0
when cast(Part2 as bigint) > 255 then 0
when cast(Part3 as bigint) > 255 then 0
when cast(Part4 as bigint) > 255 then 0
else 1
end as IsAddressValid
from PartionedAddress
;

Edited because it didn't need the check for length zero parts as the parttern check will pick up those, and it needed an additional check if there's too many full stops in the pattern.
Post #733475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse