t-sqlip-addressbit-manipulationnetmask

T-Sql: check if IP matches netmask


My SQL-Server db stores IP netmasks as binary. I need a way to match these with a given IP

For example, is 192.168.21.5 part of a netmask which is stored in the DB?

The binary representation of 192.168.21.5:

11000000.10101000.00010101.00000101 (without the dots)

The netmask stored in the DB is: binary(4) and a tinyint field:

11000000.10101000.00010101.00000000 / 24

(which would be: 192.168.21.0 /24) so, the first 24 bits of 192.168.21.5 have to match a record in the database.

How would I only check the first n bits of a binary field (similar to LEFT(text, 24))?

Is there any clever way to do this, maybe with bitwise AND?


Solution

  • declare @address binary(4) -- goal is to check if this address
    declare @network binary(4) -- is in this network
    declare @netmask tinyint   -- with this netmask in /NN format
    
    set @address = 0xC0A81505 -- 192.168.21.5
    set @network = 0xC0A81500 -- 192.168.21.0
    set @netmask = 24
    
    select
      'address matches network/netmask'
    where
      0 = (
          cast(@address as int) ^ cast(@network as int))
          &
          ~(power(2, 32 - @netmask) - 1)
          )
    

    @netmask must be between 2 and 32.