Using SQL Server, looking to get subnet from IP address and subnet mask stored in the database

We have a use of subnets for doing geo location of computer assets. In the most up to date data we do not have subnets IE 10.81.66.0, we just have the IP address and subnet mask such as 255.255.255.0

Is there a way using SQL to take a column of IP addresses like 10.81.66.99 along with the subnet mask of 255.255.255.0 and convert them into the subnets like the above 10.81.66.0. I know this is a lot easier with 24 bit mask, but I need this to work for any subnet mask.

I'm not sure if you'd want to convert the VARCHAR IP address into a binary map of 1s and 0s to do the bitwise comparison between the IP address and the subnet mask, keeping the 1s in common and then convert them back somehow? Any help would be really appreciated as this hurts my head every time I try to spend time figuring it out.

SQL version: Microsoft SQL Server 2019 (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 12:37:13

Base Query:

SELECT ComputerName ,IP ,SubnetMask FROM IPsource

Sample Output:

Computer99, 10.81.66.99, 255.255.255.0

Sample Desired Output:

Computer99, 10.81.66.99, 255.255.255.0, 10.81.66.0

Solution

You should probably have stored this data as binary(4) or int in the first place. Be that as it may, you can use PARSENAME to break up the four parts, then cast to int and use the & operator to mask out the bits. Then just concat it back together.

select
  ip.*,
  concat_ws('.', v2.masked1, v2.masked2, v2.masked3, v2.masked4) as masked
from ip
cross apply (select
  try_cast(parsename(ip.address, 4) as int),
  try_cast(parsename(ip.address, 3) as int),
  try_cast(parsename(ip.address, 2) as int),
  try_cast(parsename(ip.address, 1) as int),
  try_cast(parsename(ip.subnet, 4) as int),
  try_cast(parsename(ip.subnet, 3) as int),
  try_cast(parsename(ip.subnet, 2) as int),
  try_cast(parsename(ip.subnet, 1) as int)
) v1(address1, address2, address3, address4, subnet1, subnet2, subnet3, subnet4)
cross apply (select
  v1.address1 & v1.subnet1,
  v1.address2 & v1.subnet2,
  v1.address3 & v1.subnet3,
  v1.address4 & v1.subnet4
) v2(masked1, masked2, masked3, masked4);

db<>fiddle

The cross apply just makes it simpler to pull out individual pieces, you can of course combine it all into one expression.

IPv6 formatting is much more involved, I suggest you use SQLCLR functions for that.