While using the GeoIP tables from MaxMind, we found it useful to create a few SQL functions. To use the data, you have to convert an IP address into and IP number that you check against a list of ranges to determine a location code. The query and algorithm used to generate the ip number from an ip address is somewhat well documented. The primary example is on MaxMind's own website here.

While working with the data, we thought about how storing ip addresses in this way would both save space and optimize indexing and search queries. To store an IP address in a sql table, you would normally need 15 bytes for the 15 possible characters. This is both a lot of space, as well as a variable length field which is not desirable for best practices in table optimization.

At novcon, we store the ipnumber ranges in bigint columns, taking up just 8 bytes of storage each, instead of 15. While this sounds like a great way to store every ip address in every SQL table we have, the problem comes when you want to reverse the process and convert the network number to an ip address.

The same algorithm that was used to create the ip number from the ip address is not able to be easily reversed. We were unable to find any other examples of reversing the number back to an ip address in the searches we did.

So like any good hacker, we wrote our own....

We are including the three functions we use for the entire process below.

Function 1: Converting an IP Address to an IP Number

This process is fairly well documented, and the function below is merely our version of the MaxMind function, with very little modification.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Get IP Number from IP Address
-- =============================================

CREATE FUNCTION [dbo].[numFromIP] (
    @ip VARCHAR(15)
) RETURNS BIGINT
AS
BEGIN
    DECLARE @rv BIGINT,
        @o1 BIGINT,
        @o2 BIGINT,
        @o3 BIGINT,
        @o4 BIGINT

    SELECT
        @o1 = CONVERT(BIGINT, PARSENAME(@ip, 4)),
        @o2 = CONVERT(BIGINT, PARSENAME(@ip, 3)),
        @o3 = CONVERT(BIGINT, PARSENAME(@ip, 2)),
        @o4 = CONVERT(BIGINT, PARSENAME(@ip, 1)) 

    IF (@o1 BETWEEN 0 AND 255)
        AND (@o2 BETWEEN 0 AND 255)
        AND (@o3 BETWEEN 0 AND 255)
        AND (@o4 BETWEEN 0 AND 255)
    BEGIN
        SET @rv = (@o1 * 16777216) +
            (@o2 * 65536) +
            (@o3 * 256) +
            (@o4)

    END
    ELSE
        SET @rv = -1
    RETURN @rv
END
GO

Function 2: Reversing The Process

This function is responsible for converting an IP number back into an IP address. The process starts by translating the bigint value into a string of 32 binary digits. These digits are broken up into the 4 octets of an IP address. Each set of 8 digits is sent to another function we wrote (below) to convert the octet from base 2 to decimal.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Get IP Address from IP Number
-- =============================================
CREATE FUNCTION [dbo].[ipFromNum] 
(

	@ipnum bigint
)
RETURNS varchar(32)
AS
BEGIN

DECLARE @ipAddr varchar(15)
DECLARE @binString varchar(32)
DECLARE @counter tinyint

set @counter=32 
set @binstring = ''

while (@counter > 0)
BEGIN
	select @binstring = convert(char(1),@ipnum % 2) + @binstring
	select @ipnum = convert(int,(@ipnum/2)), @counter=@counter - 1
END

Select @ipAddr=dbo.decFromOctet(subString(@binstring,0,9)) + '.' +
dbo.decFromOctet(subString(@binstring,9,17)) + '.' +
dbo.decFromOctet(subString(@binstring,17,25)) + '.' +
dbo.decFromOctet(subString(@binstring,25,33))




	RETURN @ipAddr

END

GO

Function 3: Converting an Octet into its Decimal Value

This function is based on a baseN conversion function written by D. Patrick Caldwell It's purpose is simply to convert the string of 8 binary digits to its decimal representation.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Convert binary octet to decimal value
-- =============================================
CREATE FUNCTION [dbo].[decFromOctet] 
(
	@octet varchar(8)
)
RETURNS varchar(3)
AS
BEGIN

	DECLARE @decValue int
	declare @powerChars char(2)
	declare @index smallint;
	
	set @decValue = 0
	select @powerChars = '01',@index = 0
	
	while(@index < 8)
	BEGIN
		select @decValue = @decValue + Power(2,@index) *
			(CHARINDEX (SUBSTRING(@octet, 8 - @index, 1)
			, @powerChars) - 1
			), @index = @index + 1
	END

	RETURN convert(varchar(3),@decValue)

END

GO

We think these functions make it possible to store all IP address information as a consistent and standard decimal representation, saving both space and time. We hope you find them useful. We would like to thank MaxMind and D. Patrick Caldwell for their existing work that is the basis for these functions.

NovCon Solutions is available for SQL Optimization Consulting. For more information please call 1.877.887.4041 or email This e-mail address is being protected from spambots. You need JavaScript enabled to view it .