Converting Integer to IP format

I came across this question at ASP.NET forum http://forums.asp.net/t/1423554.aspx.

User George Mastros (gmmastros) pointed the user to a post by Denis Gobo (SQLDenis) at lessthandot.com:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-convert-ip-addresses-between-bigi

In this posting, SQLDenis provided two functions to convert between IP address and bigint number presentation of the IP.

 

The user was asking for an inline query to mimic the function after he read the posting.

Here is the conversion of Denis’s BigIntnumberToIp function:

–Test

declare @IP bigint
SET @IP =4278058234
--254.253.252.250

SELECT (CONVERT(VARCHAR, @IP / 16777216) + '.' 
+
        CONVERT(VARCHAR, CAST(@IP - (@IP / 16777216 * 16777216) as INT) / 65536) + '.' 
+
        CONVERT(VARCHAR, CAST((CAST(@IP - (@IP / 16777216 * 16777216) as INT) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) / 256) + '.' 
+
        CONVERT(VARCHAR, CAST((CAST(@IP - (@IP / 16777216 * 16777216) as int) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) - (( CAST((CAST(@IP - (@IP / 16777216 * 16777216) as int) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) / 256) * 256)))

 

By the way, here is the orginal function by SQLDenis:

CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS BIGINT)

RETURNS VARCHAR(15)

AS

BEGIN

 DECLARE @Octet1 BIGINT

 DECLARE @Octet2 TINYINT

 DECLARE @Octet3 TINYINT

 DECLARE @Octet4 TINYINT

 DECLARE @RestOfIP BIGINT

 

 SET @Octet1 = @IP / 16777216

 SET @RestOfIP = @IP – (@Octet1 * 16777216)

 SET @Octet2 = @RestOfIP / 65536

 SET @RestOfIP = @RestOfIP – (@Octet2 * 65536)

 SET @Octet3 = @RestOfIP / 256

 SET @Octet4 = @RestOfIP – (@Octet3 * 256)

 

 RETURN(CONVERT(VARCHAR, @Octet1) + ‘.’ +

        CONVERT(VARCHAR, @Octet2) + ‘.’ +

        CONVERT(VARCHAR, @Octet3) + ‘.’ +

        CONVERT(VARCHAR, @Octet4))

END

 

Advertisements

---