INET_ATON: IP Address to Number

Another Saturday in Dayton, hope everyone in the SQL community is doing well and those, returning from the PASS Summit travel safely. Today is busy as usual with the children, but I found some time to share another SQL tip. This Syntax Saturday is  about converting IP addresses to numerical values for purposes of comparison. The difficulty of course is 10.0.0.10 sorts before 10.0.0.9, naturally, versus the expected logical sort by numerical value within the 10.0.0.0 network. In databases such as MySQL, this is an easy task using the INET_ATON() function. So, this is in part a study on how that works.

The basic principle:
Given an IP Address of the form A.B.C.D, the formula for the numerical IP address conversion is:
A x 256^3 + B x 256^2 + C x 256^1 + D x 256^0

In other words, the value of the octet times 256 raised to the power of the octet number (right-to-left order) minus one. Therefore, in most implementations like MySQL, you will find that input values of form ‘A.B.C.D.E’ still return a value with A being multiplied by 256^4. Conversely, short-form IP address entries work also; however, the calculation accounts for the four-octet need. Consequently, the short-form of 127.0.0.1 (i.e., 127.1) treats the 127 as octet four and the one as octet one. Similarly, an IP of 10.128.1 would equate to ten times 256 cubed plus 128 times 256 squared plus one. Just remember that, when less than four octets are present, the last octet reading left-to-right is octet one; then, the remaining octets are placed from left-to-right in reverse numerical order.

Sample code:

Here is an example using T-SQL, taking advantage of the PARSENAME() for giggles. Using this approach, though, limits you to supporting at most a four-octet input. Given that is typically the usage, it works; however, note for a more robust approach, alternatives using a split-string method or the like would probably be best.

Listing 1

SELECT Addr
, COALESCE(P1, P2, P3, 0) * CONVERT(BIGINT, 16777216)
+ CASE WHEN P1 IS NOT NULL THEN P2 WHEN P2 IS NOT NULL THEN P3 ELSE 0 END * 65536
+ CASE WHEN P1 IS NOT NULL THEN P3 ELSE 0 END * 256
+ COALESCE(P4, 0) AS Num
FROM (
SELECT Addr
     , PARSENAME(Addr, 4) AS P1
     , PARSENAME(Addr, 3) AS P2
     , PARSENAME(Addr, 2) AS P3
     , PARSENAME(Addr, 1) AS P4
FROM (
   VALUES('192.168.0.1'),
   ('10'),
   ('10.0.1'),
   ('127.1')
) IPS(Addr)
WHERE ISNUMERIC(REPLACE(Addr,'.','')) = 1
) derived
;

Starting from the inside, notice PARSENAME() actual uses the right-to-left order indexing; therefore, in A.B.C.D, value A is octet four. To use these in formula how most people read IP addresses, I have labeled the values in reverse starting with A as P1 up to D as P4. The outer query then moves things around based on the short-form handling rules discussed above. If an octet is missing, PARSENAME() returns NULL; therefore, we can take advantage of COALESCE() or ISNULL() here. Hopefully that is clear, but please do post below for further information.

Extended usage:

Moving beyond mimicking MySQL, one important usage of this conversion is to test that an IP address falls within a specific range. For example, the individual’s scenario I helped involves a table of firewalls and another of servers. The servers table has an IP address string for each record. The firewalls table has a network address and a subnet (e.g., 192.168.0.0/24) in addition to its interface address. Easy, right? Using similar logic to above, an INET_ATON() user-defined function within SQL Server is very light work. Hmm. How, though, does one figure out what the upper bound is for a given network 192.168.0.0 based on the subnet? That is equally easy with some networking knowledge, so as a bonus I will share the mathematical formula for translating a subnet id to the total number of IP addresses.

Given a subnet N, the calculation is 2^(32-N) or two to the power of 32 minus N. Technically, the number of nodes is derived by additionally subtracting two, as the network identifier (e.g., 192.168.0.0) and the broadcast (e.g., 192.168.0.255) addresses allocations consume IPs also. It is up to each programmer’s preference. Example code here shows the shorter version, which has the advantage of 255.255.255.255 or /32 showing one IP address.

Listing 2

SELECT '/' + CONVERT(VARCHAR(2), [number]) AS SubnetID
 , POWER(CONVERT(BIGINT, 2), 32 - [number]) AS NodesPer
 FROM master..spt_values
 WHERE [type] = 'P'
 AND [number] BETWEEN 1 AND 32
;

(results)

Using the above code, the solution I provided was to use the subnet conversion formula or create a utility table of subnets, including textual representation like 255.255.0.0, and their associated nodes count. On JOINing of the firewalls and servers table, the firewall network address is simply run through INET_ATON() and then the upper end of range is that result plus NodesPer value minus one. Both tips came in handy in this scenario and I hope comes in handy for you also.

Best regards and happy coding,

Kevin