All,I have a need to determine the IP range an IP address falls in.Example:set nocount oncreate table #hub ( HubName varchar(20) , Start_Ip varchar(15) , End_Ip varchar(15) )Insert #hub Select 'Hub1' , '10.10.0.1' , '10.10.0.255'Insert #hub Select 'Hub2' , '100.10.0.1' , '100.10.0.255'Insert #hub Select 'Hub3' , '1.10.0.1' , '1.10.0.255'Insert #hub Select 'Hub4' , '001.010.000.001' , '001.010.000.255'declare @ip varchar(15)-- falls in Hub3's rangeselect @ip = '1.10.0.2'select * from #hub where @ip >= start_ip and @ip <= end_ip-- PROBLEM: falls within Hub3's range, but won't be found by SQLselect @ip = '1.10.0.6'select * from #hub where @ip >= start_ip and @ip <= end_ip-- falls in Hub3's rangeselect @ip = '1.10.0.10'select * from #hub where @ip >= start_ip and @ip <= end_ip-- padded: falls within Hub4's rangeselect @ip = '001.010.000.006'select * from #hub where @ip >= start_ip and @ip <= end_ipdrop table #hub
As you can see, the IP '1.10.0.6' clearly falls in the range '1.10.0.1' to '1.10.0.255'. However, since we are comparing varchar values, the SQL query does NOT return a match.In our production database, the HUB table contains 7,000 records (relatively small at this point, but could grow tremendously in the future).Padding the IP's would take care of the issue ('001.010.000.006' falls between '001.010.000.001' to '001.010.000.255') but how would you do this on a record by record basis (and would you want to)?This lookup will be done by help desk rep's, so the response must be quick and efficient, since multiple rep's (50-250) could be running this continuously throughout the day.One last detail: we CANNOT store the start and end IP's in the HUB table with padding -- the database is proprietary and cannot be altered. 
Determine the IP range an IP address falls in
Posted - 2001-05-31 : 12:58:39