bitvector - 8 months ago 100

SQL Question

Given a table with the following IP Addresses

`IPAddress`

-----------

192.168.1.1

192.168.1.2

192.168.1.3

192.168.1.4

192.168.1.5

192.168.1.6

192.168.1.7

192.168.1.8

I'm looking for the following output

`Output`

---------------

192.168.1.0/29

192.168.1.8/32

The closest I've come was with this:

`SELECT SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress))), COUNT(*)`

FROM IP

GROUP BY SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress)))

But that only gets me the subnet and # of address. Not sure how to take the next step.

Answer

You will need the subnet-mask in order to know the correct amount of host bits for your notation.

This will give you CIDR:

```
DECLARE @IPADDRESS VARCHAR(15) = '192.168.1.1'
,@NETMASK VARCHAR(15) = '255.255.255.0'
,@NETWORKBITS VARCHAR(3)
SET @NETWORKBITS =
CASE
WHEN @NETMASK = '255.0.0.0' THEN '8'
WHEN @NETMASK = '255.128.0.0' THEN '9'
WHEN @NETMASK = '255.192.0.0' THEN '10'
WHEN @NETMASK = '255.224.0.0' THEN '11'
WHEN @NETMASK = '255.240.0.0' THEN '12'
WHEN @NETMASK = '255.248.0.0' THEN '13'
WHEN @NETMASK = '255.252.0.0' THEN '14'
WHEN @NETMASK = '255.254.0.0' THEN '15'
WHEN @NETMASK = '255.255.0.0' THEN '16'
WHEN @NETMASK = '255.255.128.0' THEN '17'
WHEN @NETMASK = '255.255.192.0' THEN '18'
WHEN @NETMASK = '255.255.224.0' THEN '19'
WHEN @NETMASK = '255.255.240.0' THEN '20'
WHEN @NETMASK = '255.255.248.0' THEN '21'
WHEN @NETMASK = '255.255.252.0' THEN '22'
WHEN @NETMASK = '255.255.254.0' THEN '23'
WHEN @NETMASK = '255.255.255.0' THEN '24'
WHEN @NETMASK = '255.255.255.128' THEN '25'
WHEN @NETMASK = '255.255.255.192' THEN '26'
WHEN @NETMASK = '255.255.255.224' THEN '27'
WHEN @NETMASK = '255.255.255.240' THEN '28'
WHEN @NETMASK = '255.255.255.248' THEN '29'
WHEN @NETMASK = '255.255.255.252' THEN '30'
WHEN @NETMASK = '255.255.255.254' THEN '31'
WHEN @NETMASK = '255.255.255.255' THEN '32'
END
PRINT @IPADDRESS + '/' @NETWORKBITS
```