THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Converting IP Addresses Between Bigint and Varchar And How to Sort IP Addresses

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

Published Sunday, October 05, 2008 3:26 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

noeldr said:

I think it is simpler to have 4 tinyints and all sorting, converts, etc manipulations (including data integrity checks!) come in by "default"

October 6, 2008 1:06 PM
 

AaronBertrand said:

Hi Denis, I wrote about this a few years ago (2003):

http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

I don't think I dealt with the sorting issue, though.

October 6, 2008 1:54 PM
 

K. Brian Kelley said:

The reason I suggested it is years ago we had a web filtering product that used the varchar(15) approach and I still see this occasionally with network support applications. Then came a new version and we saw an integer column instead.

The main reason I think they went with this approach and not 4 tinyints (far preferable for many reasons) was they were trying to force customers to use their reporting package (it reeked). The large int is a known security obfuscation technique where attackers are trying to hide what IP they are sending a user to. So it was a simple matter to pull out the IP in octet form and write our own reports. But a lot of folks haven't seen this.

October 6, 2008 2:59 PM
 

Denis Gobo said:

@noeldr

I agree

October 6, 2008 7:31 PM
 

Denis Gobo said:

@AaronBertrand,

Not the first time I coded something only to find a better version on yout site a day later. I will blog something about reinventing the wheel possibly tomorrow

October 6, 2008 7:33 PM
 

=tg= Thomas H. Grohser said:

Hi folks, I impelented a system based on binary(4) some years ago. It uses 4 not 8 bytes per IP (if you have to store billions of rows this helps a lot) still readable in the binary format. The conversion is different but the results are the same

declare @ip as varchar(15)

declare @IPbin as binary(4)

SET @ip = '10.0.255.137'

select @IPbin = (CONVERT(binary(1), char(PARSENAME(@IP,4))) +

CONVERT(binary(1), char(PARSENAME(@IP,3)) ) +

CONVERT(binary(1), char(PARSENAME(@IP,2)) ) +

CONVERT(binary(1), char(PARSENAME(@IP,1)) ))

select @IPbin

select cast(ascii(substring(@ipbin, 1, 1)) as varchar(3))+'.'+

cast(ascii(substring(@ipbin, 2, 1)) as varchar(3))+'.'+

cast(ascii(substring(@ipbin, 3, 1)) as varchar(3))+'.'+

cast(ascii(substring(@ipbin, 4, 1)) as varchar(3))

October 7, 2008 3:06 AM
 

Leonid Reshetin said:

I suggest a little change in function IntegerToIPAddress.

If the first octet is big enough (more than 127) then the statement

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

raises 'Arithmetic overflow error converting expression to data type int.' because default type of (@Octet1 * 16777216) is int.

The follow statement works properly:

SET @RestOfIP = @IP - (@Octet1 * CAST(16777216 AS bigint))

October 8, 2008 10:30 AM
 

Mitchell said:

@=tg= Thomas H. Grohser:

when I use your T-SQL in SQL Server 2000:

cast(ascii(substring(@ipbin, 4, 1)) as varchar(3)) return NULL.

The T-SQL as follow works properly:

CAST(SUBSTRING(@Ipbin, 4, 1) AS VARCHAR(3))

May 12, 2010 1:56 AM
 

naser wishah said:

thanks

November 13, 2010 9:36 AM
 

Blake said:

This was very useful.

Thanks!

Blake

December 5, 2012 11:34 AM
 

Pushi said:

Wouldn't it be better to get the app that is recording the ip address to do the validation?

Then convert to a big int in a similar manner to your function but 'decimalise' it so you can read it

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

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

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

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

So 127.0.0.1

would become 127000000001

for visual confirmation.

I am sure someone will tell me why to do it your way - in fact i am hoping someone could take each method apart so i can use the best one ;-)

September 12, 2013 7:32 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement