Convert IPs to Binary

I'm working on some SQL to search for data based on IPs and IP Ranges. Doing this with character data in the database is horrendous. When searching through millions of records using JOINs with LIKE comparisons, the performance is completely unacceptable. So I'm working on converting the IP addresses to Binary format to do some (hopefully) faster searching. I have all of the SQL code to do this, which I'll post a little later with some performance benchmarks, but first some ColdFusion code to deal with display and conversion of IP addresses to and from binary. If you use the code, please let me know if it works out for you, and look for a further post on making use of this in SQL Server. So, without further ado, here's the function:
Posted by Daniel Short on Dec 2, 2009 at 8:09 AM | Categories: ColdFusion - SQL -

2 Comments

Seb Duggan

Seb Duggan wrote on 12/03/09 6:56 AM

I was recently setting up a geo-ip locator using a free database from the web. The way they do it is this: Take an IP address - e.g. 1.2.3.4 The numerical representation for the IP is: (1 * 256 * 256 * 256) + (2 * 256 * 256) + (3 * 256) + 4 This will give you a maximum of a 10-digit number. This makes it easier to match an IP address against a range, just by using BETWEEN in SQL.
Daniel Short

Daniel Short wrote on 12/03/09 7:30 AM

Yep, I wrote another function for doing that, which includes returning an entire range if you don't specify all four octets. Hoping to get to do some testing on that today.