How to search a IP address in a country ip range

Question

How to search a IP address in a country ip range

romanoplescia

SSCommitted

Points: 1787
More actions
November 11, 2009 at 10:27 am

#212272

I have downloaded a block of IP address range for the UK and imported into a table. What I want to do is to have proc that checks whether the ip address exists in that range when a user logs in to the website. The problem is that the IP addresses i dowloaded are in a range that appear like this, 204.231.232.0/24, which means all ip's 204.231.232.0 - 24. Would some one be able to tell me how I can pass a ip address as a condition in a select statement that will check whether it is in the range.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd have to know your actual table structure or use of this to do more, but see if this gets you going in the right direction:

declare @IPRange varchar(100);

select @IPRange = '204.231.232.0/24';

select

parsename(@IPRange, 4),

parsename(@IPRange, 3),

parsename(@IPRange, 2),

parsename(@IPRange, 1);

select

case

when parsename(@IPRange, 1) like '%/%'

then left(parsename(@IPRange, 1), charindex('/', parsename(@IPRange, 1))-1)

else parsename(@IPRange, 1)

end,

case

when parsename(@IPRange, 1) like '%/%'

then right(parsename(@IPRange, 1), len(parsename(@IPRange, 1)) - charindex('/', parsename(@IPRange, 1)))

else parsename(@IPRange, 1)

end;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

romanoplescia SSCommitted Points: 1787 More actions · Answer 2

its a very simple table with two columns, ID and IP_Address. The thing is that for a country like the UK there are about 1700 rows in the table each representing a range like 204.231.232.0/24. So what I want to do is write a trigger that fires when an insert happens on the Users table ,which has a column for the IP address, to check the IP address from the row that has just been inserted is in the range specified and if not it will run an update on that row.

So essentially what it needs to do is iterate through each row in the ipLookup table and check whether the IP Address passed to it as a parameter falls within a certain range, the only thing I don't know how to do is to check the range.

karthik6244 SSC Rookie Points: 33 More actions · Answer 3

Hi, how are you getting IP address range let say 10.9.0.0/24 then how we are getting IP range, any help on this will be great! I am working on same issue trying to get the IP Range.