Technical Article

Convert int IP-address to dot-notation

,

integer IP-address converted to varchar dot-notation

Usage SELECT dbo.IPNumberToString(-2037012288)

if exists (select 1
          from sysobjects
          where  id = object_id('dbo.IPNumberToString)
          and type in ('IF', 'FN', 'TF'))
   drop function dbo.IPNumberToString
go

create function dbo.IPNumberToString(@IPAddress int)
/**************************************************************************
DESCRIPTION: Returns dot-notation IP-address

PARAMETERS:
		(@IPAddress int)	- The int number containing a valid IP
		
RETURNS:	IP converted to varchar dot-notation
		
USAGE:         SELECT  dbo.IPNumberToString(-2037012288)
		

AUTHOR:	Andrej Mitenkov

DATE: 	29/07/2004

MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------

***************************************************************************/

RETURNS varchar(20)
begin

	DECLARE	
		@biOctetA 	bigint,
		@biOctetB	bigint,
		@biOctetC	bigint,
		@biOctetD	bigint,
    	@bIp bigint,
        @cIp        varchar(20)
        
    	SET @bIp = CONVERT(bigint, @IPAddress)
        SET @biOctetD = (@bIp & 0x00000000FF000000) / 256 / 256 / 256
        SET @biOctetC = (@bIp & 0x0000000000FF0000) / 256 / 256
        SET @biOctetB = (@bIp & 0x000000000000FF00) / 256
        SET @biOctetA = (@bIp & 0x00000000000000FF)
        
        SET @cIp = CONVERT(varchar(4), @biOctetA) + '.' +
                CONVERT(varchar(4), @biOctetB) + '.' +
                CONVERT(varchar(4), @biOctetC) + '.' +
                CONVERT(varchar(4), @biOctetD)
                
        RETURN @cIp

end
go

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating