views:

397

answers:

3

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)

Netmask stored in 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 ?

Thank you, Reinhard

+1  A: 

192.168.21.5 XOR netmask (192.168.21.0) AND 255.255.255.0 (you want only first 24 bits) should be all 0 on 'matching' ip.

Yossarian
A: 

This condition does it:

(0xffffffff - POWER(2, 32 - bits) + 1) & CAST(ip AS int) = CAST(netmask AS int)
David M
is this meant only to work with bits>=24 (Class C Netmask) ?could make it work, but not with say bits=16 (Class B)but works with bits > 24 for smaller networks
reinhard
Just checked it, and it seems to work for me for any number of bits. What problems are you getting for class B masks?
David M
+3  A: 
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.

Vadim K.