It’s a common practice to store users’ IP addresses when logging actions to an events table. Most warehouses we’ve seen have chosen to store IPs as varchars.
While this makes querying with specific IP addresses easy, it can make filtering to ranges of IPs quite difficult depending on your database. Comparing IP addresses as varchars will yield incorrect results:
-- 100 is not less than 2, but is when compared as varchars! select '220.127.116.11' < '18.104.22.168' -- => true
Filtering to a range of IP addresses requires being able to compare any two IPs. Once you can compare, it’s trivial to filter records down to those with IPs in the range you care about. Here’s how to do just that in Postgres, MySQL, SQL Server, and Redshift.
Postgres makes working with IPs very easy. It supports an inet column type, which provides native support for IPs and IP ranges. If you’re using Postgres and didn’t use the inet column type, don’t worry, you can still cast your varchars to inets to make comparisons easy:
select '192.168.1.1'::inet < '255.1.1.1'::inet
And to check if a single IP is within a range, use the << operator:
select '192.168.1.1'::inet << '192.168.0.0/16'::inet
While MySQL doesn’t have a data type for IP addresses, it’s still easy to compare IPs in MySQL. Using inet_aton you can convert your IP addresses to integers before you compare.
select inet_aton('192.168.1.1') -- => 3,232,235,777
IPv4 addresses are just nice ways of writing 32-bit numbers. Each octet of an IPv4 address represents 8 bits. Once the IPs have been converted to integers, they are easy to compare:
select inet_aton('192.168.1.1') < inet_aton('255.1.1.1')
With SQL Server there isn’t a built-in function to convert a varchar IP to an integer, but we can make one! We’ll use a function that originally came from Denis Gobo on the late SQLBlog.com:
create function dbo.IPAddressToInteger(@ip as varchar(15)) returns bigint as begin return ( convert(bigint, parsename(@ip, 1)) + convert(bigint, parsename(@ip, 2)) * 256 + convert(bigint, parsename(@ip, 3)) * 65536 + convert(bigint, parsename(@ip, 4)) * 16777216 ) end go
SQL Server doesn’t have split function, but the parsename function works great for our use case. It is normally used to extract parts of object identifiers like dbo.table1.column1. But we can use it to parse IP addresses because they are also dot-delimited:
select dbo.IPAddressToInteger('192.168.1.1') -- => 3,232,235,777
We’re multiplying the extracted octets by large numbers to form the final 32-bit number representing the IP address. These multiplications and additions are equivalent to bit shifting and binary or’ing the octets together, which we’ll also do in the Redshift section.
Like with SQL Server, Redshift requires some gymnastics to convert a varchar IP to an integer IP. First split the varchar on the dots and convert each octet to an integer. Bitshift each octet with << to it’s position in the 32-bit version of the IP address, and finally use | to binary or the four integers together.