bitvector bitvector - 6 months ago 76
SQL Question

TSQL convert list of IP addresses to CIDR notation

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
Comments