Windows Server 2008

I was wondering yesterday what else to add to the SQL Server Programming Hacks, I used twitter and asked for some inpiration and K. Brian Kelley responed with Some systems store IPs as large integers. Converting back and forth would be cool.

Interesting, I always use 4 tinyints or plain vanilla varchar(15) to store IP addresses. The nice thing when storing IP addresses in tinyint columns is that you don't have to check if the IP address is valid, a tinyint can only hold values between 0 and 255

Before we start with code let us take a sample IP address, does 127.0.0.1 look familiar? Yes that is your local IP address.

Here it is in decimal and binary
127 0 0 1
01111111 00000000 00000000 00000001

Now to convert, you would take the first value,
add the second value + 256
add the third value + (256 * 256) = 65536
add the fourth value + (256 * 256 * 256) =16777216

So in our case the select would be

select

1

+

0

* 256 +

0

* 65536 +

127

* 16777216

which is 2130706433

So to convert from IP Adress to integer is very simple, you use PARSENAME to split it up and do the math. Here is the function.

CREATE

FUNCTION dbo.IPAddressToInteger (@IP AS varchar(15))

RETURNS

bigint

AS

BEGIN

RETURN (CONVERT(bigint, PARSENAME(@IP,1)) +

CONVERT(bigint, PARSENAME(@IP,2)) * 256 +

CONVERT(bigint, PARSENAME(@IP,3)) * 65536 +

CONVERT(bigint, PARSENAME(@IP,4)) * 16777216)

END

GO

But how do you get 127.0.0.1 out of 2130706433?
It is the reversed of what we did before (surprise) so instead of multiplying we will be dividing
Here is the funcion

 

CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS bigint)

RETURNS

varchar(15)

AS

BEGIN

DECLARE @Octet1 tinyint

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

Now let's try this out, first run this

SELECT

dbo.IPAddressToInteger('127.0.0.1')

That returns 2130706433
Now run this

SELECT

dbo.IntegerToIPAddress(2130706433)

That returns 127.0.0.1

 

Sorting IP Addresses when they are stored as varchar
When you store IP Addresses in a varchar column and someone asks you to sort them it is not that obvious how to do this.
Execute the code below

 

CREATE TABLE #IpAddresses(IP VARCHAR(15))

INSERT

#IpAddresses VALUES('12.12.12.12')

INSERT

#IpAddresses VALUES('112.12.12.12')

INSERT

#IpAddresses VALUES('12.12.112.12')

INSERT

#IpAddresses VALUES('122.12.12.12')

INSERT

#IpAddresses VALUES('122.122.12.12')

INSERT

#IpAddresses VALUES('122.122.122.12')

INSERT

#IpAddresses VALUES('122.122.122.122')

INSERT

#IpAddresses VALUES('122.122.122.112')

INSERT

#IpAddresses VALUES('122.122.122.123')

Now sort this data

SELECT * FROM #IpAddresses

ORDER

BY PARSENAME(IP,4),

PARSENAME

(IP,3),

PARSENAME

(IP,2),

PARSENAME

(IP,1)

Output
--------------
112.12.12.12
12.12.112.12
12.12.12.12
122.12.12.12
122.122.12.12
122.122.122.112
122.122.122.12
122.122.122.122
122.122.122.123

As you can see, it is not correct, this is because the data is still varchar, you need to convert to integer to 'fix' this

Run this code

SELECT

* FROM #IpAddresses

ORDER

BY CONVERT(INT,PARSENAME(IP,4)),

CONVERT

(INT,PARSENAME(IP,3)),

CONVERT

(INT,PARSENAME(IP,2)),

CONVERT

(INT,PARSENAME(IP,1))

Output
--------------
12.12.12.12
12.12.112.12
112.12.12.12
122.12.12.12
122.122.12.12
122.122.122.12
122.122.122.112
122.122.122.122
122.122.122.123

Thanks to K. Brian Kelley for the inspiration and T-SQL for this post, you can also check http://www.truthsolutions.com/ to see some of his books and articles