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?
Thanks for the feeback. Please consider sharing with others.