Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
talmon
Starting Member
1 Post |
 Posted - 2007-11-21 : 18:46:53
|
I am an amature SQL programmer and need some help.I have a table of IP ranges that looks like:NodeName IP_Start IP_EndUSA 10.1.1.0 10.1.1.3 USA 10.1.16.0 10.1.23.255 GER 10.10.10.0 10.10.10.255 GER 10.2.2.0 10.2.2.255 ...and a table with computer properties that has each computer's respective IP Address.I want to query what NodeName each computer's IP belongs to. Your help is appretiated. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-21 : 18:50:50
|
[code]Select a.IPAddress, b.NodeNameFROM IPAddressTable a inner join IPRanges b on a.IP_Start = b.IP_Start and a.IP_End = b.IP_End[/code]Assumes that the IP address fields structure the same as your sample...
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-21 : 19:02:24
|
You haven't really given enough information, but here is my best guess:The issue will be what data types you're using for the IP addresses. I assumed you're using character columns, so then you'd need to convert the addresses into integers before you can compare the ranges. Fortunately that's not too difficult and there is a nice article that tell's you how, herehttp://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.htmlGo to that article and get the code for the ipStringToInt function and then use this query:SELECT NodeName, ComputerAddressFROM IPRangesINNER JOIN ComputerProperties ON dbo.ipStringToInt(ComputerIP) BETWEEN dbo.ipStringToInt(IP_Start) AND dbo.ipStringToInt(IP_End) |
|
|
|
|
|
|
Find an IP Address in a table of IP Ranges