Andrew Pallant

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

The post MaxMind GEOIP Look-up for Microsoft SQL appeared first on Software Developer In London.