MaxMind GEOIP Look-up for Microsoft SQL

Recently I implemented a GEOIP solution on a client site; however, the site was built on a Microsoft SQL database engine and I could not find a solution for looking up the country name easily.  Through some quick Google searches I had tripped on the site for which I started at ( http://dev.maxmind.com/geoip/csv ).  The site maxmind.com gave me the MySQL solution and the mathematical solution for creating the integer.  Based on this information, I created my own function in MS SQL to retrieve me the calculated IP integer for easier use.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Andrew Pallant ( www.ldndeveloper.com )
-- Create date: 19 / 04 / 2013
-- Description: Used as a reference: http://dev.maxmind.com/geoip/csv
-- =============================================
CREATE FUNCTION fetchCountryINT
(
@IP as varchar(30)
)
RETURNS numeric(16,0)
AS
BEGIN
declare @o1 numeric(16,0)
declare @o2 numeric(16,0)
declare @o3 numeric(16,0)
declare @o4 numeric(16,0)
declare @CountryInt numeric (16,0)
declare @LastIndex int

-- Get 1st Segment
set @LastIndex = CHARINDEX('.',@IP)
select @o1 = SUBSTRING(@IP,1, @LastIndex-1)

-- Get 2nd Segment
set @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)
set @LastIndex = CHARINDEX('.',@IP)
select @o2 = SUBSTRING(@IP,1, @LastIndex-1)

-- Get 3rd Segment
set @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)
set @LastIndex = CHARINDEX('.',@IP)
select @o3 = SUBSTRING(@IP,1, @LastIndex-1)

-- Get 4th Segment
set @IP = SUBSTRING(@IP,@LastIndex+1, LEN(@IP) - @LastIndex)
set @LastIndex = CHARINDEX('.',@IP)
select @o4 = @IP

-- Calculate Integer
select @CountryInt = (@o1 * 16777216 ) + (@o2 * 65536) + (@o3 * 256) + @o4

RETURN @CountryInt
END
GO

How to use the function in a SQL statement:

select country_name from GEOIP where dbo.fetchCountryINT('174.36.207.186') between begin_ip_num AND end_ip_num

Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http://www.unlatched.com/#sthash.8DiTkpKy.dpuf

Posted in Developement, How To, SQL, Web Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*