Reputation: 21108
I have a MS SQLServer 2005 table which includes an ipaddress column, and I would like to count entries by subnet.
Unfortunately the ipaddress column is a varchar with sample data like: 192.168.10.20 10.1.2.3
I think the best approach would be to convert the ipaddress to an integer, and then I could apply the appropriate bitmask.
Are there any suggestions on how to get a string of 4 octets into an integer?
Upvotes: 0
Views: 165
Reputation: 21108
To convert a 4 octtet dotted notation to a BIGINT we can do the following.
DECLARE @IP CHAR(15) SET @IP='192.168.123.123'
select (CONVERT(BIGINT, PARSENAME(@IP,1)) + CONVERT(BIGINT, PARSENAME(@IP,2)) * 256 + CONVERT(BIGINT, PARSENAME(@IP,3)) * 65536 + CONVERT(BIGINT, PARSENAME(@IP,4)) * 16777216)
from there it's a pretty simple thing to AND that with 4294967040 (255.255.255.0) to get /24 subnet
Thanks to Less Than Dot for the code.
Upvotes: 1
Reputation: 15685
you could also do this by using PARSENAME function as it is show here.
Upvotes: 0