XML Feeds
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 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
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
Thanks to K. Brian Kelley for the inspiration for this post, you can also check http://www.truthsolutions.com/ to see some of his books
And also check out the related Order IP Addresses wiki article which I wrote a while ago
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.