Convert int IP-address to dot-notation

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:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/
RETURNS varchar(20)
begin

DECLARE
@biOctetA bigint,
@biOctetBbigint,
@biOctetCbigint,
@biOctetDbigint,
    @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