Lamprey13 (7/10/2009)
Nice article. I've used other commercial geo-location products in the past and it is nice to have a free solution that is able to get reasonable results for BI tasks without spending an arm and a leg. 🙂One comment about the ConvertIP2Num function, it seems overly complicated to me. Perhaps something a little more simple might help readability and possibly enhance performance? Here is a relatively concise way to do the conversion:
DECLARE @IPAddress VARCHAR(15)SET @IPAddress = '196.169.25.3'
--Convert to number
SELECT
CAST(PARSENAME(@IPAddress, 4) AS BIGINT) * POWER(256, 3)
+ CAST(PARSENAME(@IPAddress, 3) AS BIGINT) * POWER(256, 2)
+ CAST(PARSENAME(@IPAddress, 2) AS BIGINT) * POWER(256, 1)
+ CAST(PARSENAME(@IPAddress, 1) AS BIGINT) * POWER(256, 0)
I think both functions can be simplified
create function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin
return ((cast(parsename(@ip,4) as bigint)*256+
cast(parsename(@ip,3) as bigint))*256+
cast(parsename(@ip,2) as bigint))*256+
cast(parsename(@ip,1) as bigint)
end
create function dbo.ConvertNum2Ip(@ip bigint)
returns nvarchar(15)
as
begin
return cast(@ip/16777216 as nvarchar(15)) + '.' +
cast((@ip/65536)%256 as nvarchar(15)) + '.' +
cast((@ip/256)%256 as nvarchar(15)) + '.' +
cast(@ip%256 as nvarchar(15))
end
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537