In MSSQL its possible to convert an ip address on the fly so that it makes it possible to do ip range search on text based ip addresses. I found out about this on anotherposted 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 query 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
Did You find this page useful?
Yes
No