![]() OR ALTER FUNCTION dbo.IPv4SubnetContainsIPAddress AS AS AS VARCHAR(15) RETURN you create the following additional function the conversion becomes easy CREATE FUNCTION NVARCHAR(18), - 'eg: NVARCHAR(15) - 'eg: '192.168.0.1' '/24' is a shorthand way of writing 255.255.255.0 - ie a 32bit number with the first 24bits set to 1 (and the remaining 8 bits set to 0) CREATE FUNCTION TINYINT - max = 32 ![]() To make this a bit easier for you you'll probably want a function that can convert '/24' to a BigInt too. RETURN you can easily implement a function to test if an address is in a subnet: CREATE FUNCTION NVARCHAR(15), - 'eg: NVARCHAR(15), - 'eg: '255.255.255.0' for NVARCHAR(15) - 'eg: '192.168.0.1' ![]() Therefore I'd propose you create a function that converts your IP addresses to BIGINT datatype firstly CREATE FUNCTION NVARCHAR(15) - should be in the form '123.123.123.123' SQL Server cannot do a bitwise and between 2 binary numbers unfortunately, but it works fine between decimal representations (ie when converted to BIGINT datatype). The key to understanding this is to realise that IP addresses (and subnet masks) are just 32 bit numbers.Ī bitwise and between 2 32 bit numbers creates a new 32 bit number with a 1 in the position where there was a 1 in both of the 2 numbers being compared, and a 0 otherwise.ĮG: 1010 & 1100 = 1000 because the first digit is 1 in both numbers (yielding a 1 in the result for the first digit), but the 2nd 3rd and 4th digits are not (so give 0 in the result for the 2nd 3rd and 4th digits). ![]() If this comparison is true the test address is within the subnet You want to do exactly what a computer would do to determine if an ip address is in a subnet- ie:ġ) convert the network address, subnet mask and test address to binary.Ģ) Check if (Network Address & Subnet Mask) = (Test Address & Subnet mask) Class A, B, and C networks have natural masks, or default subnet masks: Class A: 255.0.0.0 Class B: 255.255.0.0 Class C: 255.255.255.0 You can determine the number and type of IP addresses any given local network requires based on its default subnet mask. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |