| Author |
Topic |
|
nstout
Starting Member
8 Posts |
 Posted - 2002-08-28 : 17:41:55
|
| Is it possible to convert a varchar(15) IP address to an integer? If so, anyone have a handy stored procedure that can do this quickly? And would doing so (converting, that is) allow me to compare the IP address to an IP address range?In other words:I want to convert 192.168.1.24 to an integer equivalent.Then I want to compare the integer equivalent to a range identified by a starting integer (converted IP address) and an ending integer (also a converted IP address).Ideas? Thanks! |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-28 : 17:45:40
|
this would do it select cast(replace('192.168.1.24','.','') as int)HTHJasper Smith |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-29 : 08:29:43
|
| Several issues here will give you problems.First, an IP address is not a varchar(15) - you can choose to represent it that way to make it human-readable, but you lose the ability to manipulate it easily. An IP address is a binary(4).Second, stripping off the periods in an IP address does not make a meaningful int, certainly not one you could use for range comparisons. Consider a start and end range of 192.168.1.34 - 155. 192.168.2.45 is not in that range, but running:if ( 192168245 between 192168134 and 1921681155 ) print 'uh oh'shows the problem.Lastly, if the IP ranges you want to check represent subnets, you might find a better method for checking inclusion to be a bit mask.Jonathan Boott, MCDBA{0} |
|
|
|
nstout
Starting Member
8 Posts |
Posted - 2002-08-29 : 08:34:27
|
| That gets me halfway to where I want to be. That would in fact give me an integer version of an IP address... but would not allow me to successfully compare a converted IP address to a range. See example below:IP Address to be converted = 1.1.1.23 --> 11123Range: 1.1.1.1 (1111) to 1.1.2.1 (1121)Compare statement would result in FALSE because 11123 does not fall between 1111 and 1121 although the IP address should fall within the defined range.Any way to improve on your idea so that it always works? Maybe padding with zero's? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-29 : 08:39:34
|
| Just to make sure we're on the same page - I'm not advocating an integer-cast solution, and I was asking if an arbitrary range comparison is truly what you're after (vs a subnet inclusion test). When you say "any way to improve your idea" you're hopefully referring to jasper's post.Jonathan Boott, MCDBA{0} |
|
|
|
nstout
Starting Member
8 Posts |
Posted - 2002-08-29 : 08:58:18
|
| The "any way to improve on your idea" was directed at jasper_smith's quote... however, I'm open to any way to improve my situation.Ultimately, I am seeking to compare an IP address to a subnet for inclusion. The binary solution seems to make sense... but now we're treading on parts of my brain that I haven't used since college!I have a creative but very slow existing process that takes a subnet range, builds a temporary table and fills it with all of the possible IP addresses within that subnet, and then does a simple comparison between the IP address and the temporary table to test for inclusion.The whole ugly process is based on VARCHAR(15) types... and it's terribly slow because I'm comparing the IP address to about 300 subnets.Any ideas on how to restructure the database and improve my stored procedures would be a blessing! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-29 : 09:08:44
|
| Ok ... yes the binary solution is much better and faster.An IP address is a 32-bit quantity. One group of bits represents the network, the other group the host. The netmask tells your computer where the demarcation is between the network bits and the host bits.Consider a netmask of 255.255.255.192, or in binary:11111111.11111111.11111111.11000000This means 26 bits comprise the network portion, and 6 bits the host. 2^6 = 64, minus 2 for reserved address ( 0 and 255 for 'this' and broadcast ) yields 62 possible addresses.When your computer sends packets to a remote machine, it performs a bitwise AND of the destination IP and the netmask to see if it's on the same network. If it is, the packet(s) are sent directly; otherwise they are sent to the gateway to be routed. So you could perform the same bitwise AND ( the & operator in SQL Server ) to see if a given address is on a given network, which is equivalent to asking if it falls within a given subnet range.Jonathan Boott, MCDBA{0} |
|
|
|
nstout
Starting Member
8 Posts |
Posted - 2002-08-29 : 09:17:30
|
Great... that makes sense. All I need then is a method to convert my varchar(15) IP addresses to binary format. Anyone have a handy method (or stored procedure) for converting varchar(15) IP addresses to binary?  |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2002-08-29 : 09:45:43
|
| There is an script in asp that does exactly this, and it looks like this:<% function CLngIP(ByVal asNewIP) Dim lnResults Dim lnIndex Dim lnIpAry ' Split the IP address using the dot as a delimiter lnIpAry = Split(asNewIP, ".", 4) ' Loop through Each number In the IP address For lnIndex = 0 To 3 ' if we are Not working With the last number... if Not lnIndex = 3 Then ' Convert the number To a value range that can be parsed from the others lnIpAry(lnIndex) = lnIpAry(lnIndex) * (256 ^ (3 - lnIndex)) End if ' Add the number To the results lnResults = lnResults + lnIpAry(lnIndex) Next ' if storing number within an Access Database, ' The variable Type "Long" ranges from -2147483648 To 2147483647 ' You will need To subtract 2147483648 from the number ' before querying the database. lnResults = lnResults - 2147483648 ' Return the results CLngIP = lnResults End function function CStrIP(anNewIP) Dim lsResults ' Results To be returned Dim lnTemp ' Temporary value being parsed Dim lnIndex ' Position of number being parsed ' if pulling number from an Access Database, ' The variable Type "Long" ranges from -2147483648 To 2147483647 ' You will first need To add 2147483648 to the number to parse correctly. anNewIP = anNewIP + 2147483648 ' Parse highest numbers first For lnIndex = 3 To 0 Step -1 ' Parse the current value For this position lnTemp = Int(anNewIP / (256 ^ lnIndex)) ' Append the number To the final results delimited by a dot lsResults = lsResults & lnTemp & "." ' Remove the number that we just parsed anNewIP = anNewIP - (lnTemp * (256 ^ lnIndex)) Next ' Cut off last dot lsResults = Left(lsResults, Len(lsResults) - 1) ' Return the results CStrIP = lsResults End functionsIp = "241.31.154.231" response.write CLngIP (sIp) & "<br>"response.write CStrIP (CLngIP (sIp) )%>(sorry, but I can't give the right credits to the creator of this script, because I can't find the site anymore where I grapped it from)maybe you (or someone else) can convert it into a SP, or if you are using ASP, just copy-past ;) |
|
|
|
nstout
Starting Member
8 Posts |
Posted - 2002-08-29 : 10:06:05
|
| Perfect! This is exactly what I needed. Thanks for your help! |
|
|
|
|
How do I convert IP address from varchar to int?