Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Converting IP Addresses Between Bigint and Varchar

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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 reverse of what we did before (surprise) so instead of multiplying we will be dividing. The "modulo 256" part simply removes the bytes above the one we're interested in so we only get numbers in the correct range of 0 - 255.

tsqlLine number Off | Hide | Select all