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 '100.1.1.1' < '2.1.1.1'
-- => 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

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

MySQL 

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')

SQL Server

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.

Redshift

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.

Tags: