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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s