Wednesday, December 28, 2005

[sql] IP to Country

Notes on my recent pet project to lookup country name from IP via SQL:

1) Download the IP to Country mapping data from here:

2) Load the data into a table, let's call it IP_to_Country

3) Add two columns to the data tabe, IPNumber and Country

4) Update those two columns to get country name

update DataTable
set IPNumber = (16777216.0 * parsename(client_ip,4)+
65536 * parsename(client_ip,3)+
256 * parsename(client_ip,2)+

update DataTable
set CountryName = (select I.CountryName from IP_to_Country I
where I.IPRangeStart <= IPNumber and I.IPRangeEnd >= IPNumber)