
<p>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.</p>



<p>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:</p>



<pre class="wp-block-code"><code>-- 100 is not less than 2, but is when compared as varchars!
select '100.1.1.1' &lt; '2.1.1.1'
-- => true</code></pre>



<p>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.</p>



<h3 class="wp-block-heading"><strong>Postgres</strong></h3>



<p>Postgres makes working with IPs very easy. It supports an <a href="https://www.postgresql.org/docs/9.1/datatype-net-types.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">inet</a> 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:</p>



<pre class="wp-block-code"><code>select '192.168.1.1'::inet &lt; '255.1.1.1'::inet</code></pre>



<p>And to check if a single IP is within a range, use the &lt;&lt; operator:</p>



<pre class="wp-block-code"><code>select '192.168.1.1'::inet &lt;&lt; '192.168.0.0/16'::inet</code></pre>



<h3 class="wp-block-heading"><strong>MySQL&nbsp;</strong></h3>



<p>While MySQL doesn’t have a data type for IP addresses, it’s still easy to compare IPs in MySQL. Using <a href="https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet-aton" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">inet_aton</a> you can convert your IP addresses to integers before you compare.</p>



<pre class="wp-block-code"><code>select inet_aton('192.168.1.1')
-- => 3,232,235,777 </code></pre>



<p>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:</p>



<pre class="wp-block-code"><code>select inet_aton('192.168.1.1') &lt; inet_aton('255.1.1.1')</code></pre>



<h3 class="wp-block-heading"><strong>SQL Server</strong></h3>



<p>With SQL Server there isn’t a built-in function to convert a varchar IP to an integer, but we can make one! We&#8217;ll use a function that originally came from Denis Gobo on the late SQLBlog.com:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p> SQL Server doesn’t have&nbsp;<strong>split</strong>&nbsp;function, but the&nbsp;<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?redirectedfrom=MSDN&amp;view=sql-server-ver15" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">parsename</a>&nbsp;function works great for our use case. It is normally used to extract parts of object identifiers like&nbsp;<em><strong>dbo.table1.column1</strong></em>. But we can use it to parse IP addresses because they are also dot-delimited: </p>



<pre class="wp-block-code"><code>select dbo.IPAddressToInteger('192.168.1.1')
-- => 3,232,235,777</code></pre>



<p>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.</p>



<h3 class="wp-block-heading"><strong>Redshift</strong></h3>



<p>Like with SQL Server, Redshift requires some gymnastics to convert a varchar IP to an&nbsp; integer IP. First split the varchar on the dots and convert each octet to an integer. Bitshift each octet with &lt;&lt; to it’s position in the 32-bit version of the IP address, and finally use | to binary or the four integers together.</p>


Comparing IP Addresses in SQL

SQL Superstar

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article