As an addendum to the previous article on storing IP Addresses as IP Numbers in SQL, we would like to show the same process, but in fully 32bit storage. The reason we chose to store a 32 bit IP address in a 64 bit integer (bigint) column is because there is not an unsigned integer type in MS SQL Server at this time. However, using a technique mentioned by Charles Bretana here, we can still store an IP address in a 32 bit column. This reduces the bytes required for storing IP addresses 50%.

The method simply involves taking a 32bit unsigned (positive only) integer and subtracting 2,147,483,648 to ensure that you are within a 32 bit signed integer range.

The scripts below are modified using the method described above.

Function 1: Converting an IP Address to an IP Number

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Get IP Number from IP Address
-- =============================================

CREATE FUNCTION [dbo].[numFromIP] (
    @ip VARCHAR(15)
) RETURNS INT
AS
BEGIN
    DECLARE @rv INT,
	@rvTemp	BIGINT,
        @o1 BIGINT,
        @o2 BIGINT,
        @o3 BIGINT,
        @o4 BIGINT

    SELECT
        @o1 = CONVERT(BIGINT, PARSENAME(@ip, 4)),
        @o2 = CONVERT(BIGINT, PARSENAME(@ip, 3)),
        @o3 = CONVERT(BIGINT, PARSENAME(@ip, 2)),
        @o4 = CONVERT(BIGINT, PARSENAME(@ip, 1)) 

    IF (@o1 BETWEEN 0 AND 255)
        AND (@o2 BETWEEN 0 AND 255)
        AND (@o3 BETWEEN 0 AND 255)
        AND (@o4 BETWEEN 0 AND 255)
    BEGIN
        SET @rvTemp = (@o1 * 16777216) +
            (@o2 * 65536) +
            (@o3 * 256) +
            (@o4)

	Set @rvTemp = @rvTemp - 2147483648
	set @rv = @rvTemp
    END
    ELSE
        SET @rv = -1
    RETURN @rv
END
GO

Function 2: Reversing The Process

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Get IP Address from IP Number
-- =============================================
CREATE FUNCTION [dbo].[ipFromNum] 
(

	@ipnumIn int
)
RETURNS varchar(32)
AS
BEGIN
Declare @ipNum bigint
DECLARE @ipAddr varchar(15)
DECLARE @binString varchar(32)
DECLARE @counter tinyint

set @ipNum = @ipnumIn + 2147483648

set @counter=32 
set @binstring = ''

while (@counter > 0)
BEGIN
	select @binstring = convert(char(1),@ipnum % 2) + @binstring
	select @ipnum = convert(int,(@ipnum/2)), @counter=@counter - 1
END

Select @ipAddr=dbo.decFromOctet(subString(@binstring,0,9)) + '.' +
dbo.decFromOctet(subString(@binstring,9,17)) + '.' +
dbo.decFromOctet(subString(@binstring,17,25)) + '.' +
dbo.decFromOctet(subString(@binstring,25,33))




	RETURN @ipAddr

END

GO

Function 3: Converting an Octet into its Decimal Value

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		NovCon Solutions
-- Create date: 1.10.2010
-- Description:	Convert binary octet to decimal value
-- =============================================
CREATE FUNCTION [dbo].[decFromOctet] 
(
	@octet varchar(8)
)
RETURNS varchar(3)
AS
BEGIN

	DECLARE @decValue int
	declare @powerChars char(2)
	declare @index smallint;
	
	set @decValue = 0
	select @powerChars = '01',@index = 0
	
	while(@index < 8)
	BEGIN
		select @decValue = @decValue + Power(2,@index) *
			(CHARINDEX (SUBSTRING(@octet, 8 - @index, 1)
			, @powerChars) - 1
			), @index = @index + 1
	END

	RETURN convert(varchar(3),@decValue)

END

GO