I work with IP addresses every day and Alteryx doesn't have built-in functions to handle IP addresses. However, it has every function you can imagine to work with Integers.
The Marquee Crew gave us this lifesaver a couple of years ago: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Use-RegEx-and-build-a-Macro-to-Vali...
This macro lets you validate than an IP address is a valid IP, whether it is a public or private IP address, and can also sort them.
I had a use case that fell outside of this.
I needed to determine if an IP address was part of a range and, if it was, create two new ranges on either side of it.
For example:
- Given an IP range: 10.10.0.0 to 10.10.255.255
- Is this IP is in the range: 10.10.23.7
The dotted decimal notation we use for IP addresses is just a user-friendly way of representing a 32-bit binary number.
Using the IntToBin() function and some string concatenation, you can convert an IP to its binary equivalent. You have to PadLeft with 0 to make sure each section is a full 8 bits.
| IP Address | Binary Notation |
| 10.10.23.7 | 00001010000010100001011100000111 |
padleft(toString(intToBin(toNumber([_CurrentField_]))),8,'0')
The you just concatenate them all together to get a 32-bit binary number.
PadLeft(([Octet 1 Binary] + [Octet 2 Binary] + [Octet 3 Binary] + [Octet 4 Binary]),32,'0')
Easy.
The next step is using BinToInt() to convert the binary to an integer.
| IP Address | Binary Notation | Integer Notation |
| 127.0.0.1 | 01111111000000000000000000000001 | 2130706433 |
| 10.10.23.7 | 00001010000010100001011100000111 | 168433415 |
| 172.12.68.125 | 10101100000011000100010001111101 | -1408482179 |
| 192.168.85.75 | 11000000101010000101010101001011 | -1062709941 |
Wait a second. Why do we have negative Integers?
Standard Binary math uses a leading 1 as the indicator for negative numbers. I can't just add a leading 0 because then it wouldn't be a 32-bit Integer, it'd be a 33-bit Integer.
A 32-bit integer with a +/- is just an integer, but an unsigned, 32-bit integer is called a DWORD.
The fix is to add 2^32 (4,294,967,296) to the negative Integer which gives this chart.
| IP Address | Binary Notation | Integer Notation | DWORD Notation |
| 127.0.0.1 | 01111111000000000000000000000001 | 2130706433 | 2130706433 |
| 10.10.23.7 | 00001010000010100001011100000111 | 168433415 | 168433415 |
| 172.12.68.125 | 10101100000011000100010001111101 | -1408482179 | 2886485117 |
| 192.168.85.75 | 11000000101010000101010101001011 | -1062709941 | 3232257355 |
Okay. Now what?
Let's use the IP's I mentioned at the beginning of this post and throw in 10.11.87.105 for good measure.
| IP Address | DWORD Notation |
| 10.10.0.0 | 168427520 |
| 10.10.255.255 | 168493055 |
| 10.10.23.7 | 168433415 |
| 10.11.87.105 | 168515433 |
The first two are the range and the second two are the test candidates.
Let's label them.
| Label | IP Address | DWORD Notation |
| RangeStart | 10.10.0.0 | 168427520 |
| RangeEnd | 10.10.255.255 | 168493055 |
| Test1 | 10.10.23.7 | 168433415 |
| Test2 | 10.11.87.105 | 168515433 |
Now you can have a Boolean called IsInRange and have
IF [Test#] >= RangeStart and [Test#] <= RangeEnd THEN 1 ELSE 0 ENDIF
Test1 would be TRUE and Test2 would be FALSE.
I have another Macro to convert Integer back to IP address dotted decimal notation.
The actual work I do with these IPs as Integers is too complex to include here and still a work in progress. I am trying to use these number to build new ranges that exclude specific IP addresses or ranges of IP addresses. I can easily add or subtract 1 from an Integer and convert that back to IP address using these macros.
* How to determine if an IP address is valid - https://www.oreilly.com/library/view/regular-expressions-cookbook/9780596802837/ch07s16.html
