T-SQL:检查IP是否与网络掩码匹配

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 必须介于2和32之间。

192.168.21.5 XOR netmask (192.168.21.0) AND 255.255.255.0 (您只需要前24位)应该是全部 0 在“匹配”IP上。

这种情况会:

(0xffffffff - POWER(2, 32 - bits) + 1) & CAST(ip AS int) = CAST(netmask AS int)

如果你想让它与蒙版0或1位一起工作,那么你需要将电源(2,32-@netmask)更改为 Power(强制转换(2为bigint),32-@netmask) 拜伊