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
?
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.