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



Last Modified: 21 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-29 - MSSQL - TRIM Function