query ipaddresses by subnet

Question

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?

Answer 1

you could also do this by using PARSENAME function as it is show here.

Answer 2

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.