MSSQL - Convert IP To big int

17. February 2011 20:53

In MSSQL its possiblt to convert an ip address on the fly so that it makes it possible todo ip range search on text based ip addresses. I found out about this on another posted article. However it has a little issue if you want to scale.

However i did't like the method that was used. As its doing the compares it is extremly slow to search on calculated values all the time. So i came up with the following functions. I dropped the use of the PARSENAME function because it isn't deterministic. In these functions you can create a pre computed field on existing data on the function, add an index, then perform the ip range quriy using index seek's vs an index scan. Or you could just simply convert the ip address into a bigint and store it into the table instead, this would save a lot of space on larger table's.

 

CREATE FUNCTION [dbo].[IPToBINT] (@ip varchar(max)) 
	RETURNS bigint
	WITH SCHEMABINDING
AS
BEGIN
	DECLARE @idx1 int
	DECLARE @idx2 int
	DECLARE @idx3 int
	DECLARE @idx4 int
	DECLARE @ret bigint
	
	SELECT @idx1 = CHARINDEX('.', @ip)
	SELECT @idx2 = CHARINDEX('.', @ip, @idx1+1);
	SELECT @idx3 = CHARINDEX('.', @ip, @idx2+1);
	
	SELECT @ret = CONVERT(bigint, SUBSTRING(@ip, 0, @idx1)) * POWER(2, 24) +
					CONVERT(bigint, SUBSTRING(@ip, @idx1 + 1, @idx2 - @idx1 - 1)) * POWER(2, 16) +
					CONVERT(bigint, SUBSTRING(@ip, @idx2 + 1, @idx3 - @idx2 - 1)) * POWER(2, 8) +
					CONVERT(bigint, SUBSTRING(@ip, @idx3 + 1, LEN(@ip) - @idx3))
	RETURN @ret
END
GO

CREATE FUNCTION [dbo].[BINTToIP] (@ip bigint)
	RETURNS varchar(16)
BEGIN
	DECLARE @ret varchar(16)
SELECT
	@ret = 	CONVERT(varchar, @ip / POWER(2, 24)) + '.' +
				CONVERT(varchar, @ip  / POWER(2, 16) & 0xFF) + '.' +
				CONVERT(varchar, @ip / POWER(2, 8) & 0xFF) + '.' +
				CONVERT(varchar, @ip & 0xFF)
	RETURN @ret
END
GO

 

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (2)+