THE SQL Server Blog Spot on the Web

Welcome to - 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 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


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




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

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

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



But how do you get 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)



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))


Now let's try this out, first run this

SELECT dbo.IPAddressToInteger('')

That returns 2130706433
Now run this

SELECT dbo.IntegerToIPAddress(2130706433)

That returns


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



INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

INSERT #IpAddresses VALUES('')

Now sort this data

SELECT * FROM #IpAddresses






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






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

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



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

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:


I agree

October 6, 2008 7:31 PM

Denis Gobo said:


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 = ''

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:


May 12, 2010 1:56 AM

naser wishah said:


November 13, 2010 9:36 AM

Blake said:

This was very useful.



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)


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

SQL Guy said:

@Pushi - LOL

July 16, 2015 4:37 PM
New Comments to this post are disabled

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


Privacy Statement