ChrisM@Work (8/14/2012)
Here's an alternative using PARSENAME()
SELECT
bi.Building_Name,
COUNT(di.DeviceNum) NumberOfDevices
FROM #Building_Information bi
INNER JOIN #Device_Information di
ON PARSENAME(bi.Subnet,4) = PARSENAME(di.IP_Address,4)
AND PARSENAME(bi.Subnet,3) = PARSENAME(di.IP_Address,3)
AND PARSENAME(bi.Subnet,2) = PARSENAME(di.IP_Address,2)
GROUP BY Building_Name;
Only issue with using parsename() is that it is slow and will disallow the use of indexes on the joining columns if they exist.