Convert IP String to numeric representation and numeric representation to IP String in SQL Server

In the post Convert IP String to numeric representation and numeric representation to IP String in Java we dealt with IP conversions in Java. In this post we will do just the same, but instead using Java we will use SQL Server. Converting the Java code to MS SQL is very simple. Not much knowledge in T-SQL is needed, because we are mostly dealing with mathematical operations. We will simply create 2 functions. Here is the first function for converting bigint to IP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[bigint_to_ip]
(
        @ip bigint
)
RETURNS varchar(15)
AS
BEGIN
    DECLARE @octet0 varchar(3)
    DECLARE @octet1 varchar(3)
    DECLARE @oct
et2 varchar(3)
    DECLARE @octet3 varchar(3)
    SET @octet3 = (@ip / power(2, 24)) % 256
    SET @octet2 = (@ip / power(2, 16)) % 256
    SET @octet1 = (@ip / power(2, 8)) % 256
    SET @octet0 = @ip % 256
    RETURN @octet3 + '.' + @octet2 + '.' + @octet1 + '.' + @octet0
END

The second function which converts IP to bigint is a bit more tricky, since unlike java “split” function, SQL Server T-SQL doesn’t present any comfortable way of handling strings in the manner Java does. Luckily, SQL Server does have a function called “PARSENAME” which is used for getting a part of an object. Assuming the parts of an object are separated with dots, it is possible to get any part of the object. for example:

DECLARE @myObject varchar(100)
SET @myObject = 'this.is.my.object'
PARSENAME(@ObjectName, 4) -- Return: this
PARSENAME(@ObjectName, 3) -- Return: is
PARSENAME(@ObjectName, 2) -- Return: my
PARSENAME(@ObjectName, 1) -- Return: object

Note, that the position is reversed to the intuitive logic, and that position “1” means the last part of the string and not the first.

We can use the “PARSENAME” function in order to easily split the IP string to its components. Here is the second function that converts IP to bigint:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ip_to_bigint]
(
        @ip varchar(15)
)
RETURNS bigint
AS
BEGIN
    DECLARE @octet0 bigint
    DECLARE @octet1 bigint
    DECLARE @octet2 bigint
    DECLARE @octet3 bigint
    SET @octet3 = ParseName(@ip, 4)
    SET @octet2 = ParseName(@ip, 3)
    SET @octet1 = ParseName(@ip, 2)
    SET @octet0 = ParseName(@ip, 1)
    RETURN @octet3 * power(2, 24) + @octet2 * power(2, 16) + @octet1 * power(2, 8) + @octet0
END

Convert IP String to numeric representation and numeric representation to IP String in Java

There are times we would like to convert IP string to its numeric representation. For example, if we would like to store in our database ranges of IPs and for each IP its Geo location or organization or ISP or any other IP based service. Storing the IP as a String doesn’t have much meaning, since we won’t be able to do range comparisons. For example if we had a database table named “country_ip” with the following columns:

• begin_ip – Beginning of the IP range (long/bigint)
• end_ip – End of IP range (long/bigint)
• country_code – Country code corresponding to the IP range (2 or 3 characters depending on the standard used)

Note that the “being_ip” and “end_ip” are in their numeric representation and are stored as long/bigint values in the database.

Now, suppose we have an IP that we would like to find its country code.

First we will have to convert the IP to its numeric representation and then we would find the country code by writing a simple SQL query:

select country_code from country_ip where :param_ip >= begin_ip and :param_ip <= end_ip

The IP conversion functions, which convert an IP to its numeric representation and vise versa, are quite simple. A string IP is constructed from 4 octets separated by “.” (of course, this is going to change gradually with the entrance of IPv6, but as it is for now all services are still in IPv4). Each octet is a byte value from 0 to 255. In order to convert a string IP to its numeric representation all we have to do is split the IP string to its 4 octets and then put each octet into a long variable on its correct position. For example, the first octet from the right is entered as it is, from the “0” bit to the “7” bit, the second (from the right), should be placed on the “8” bit to the “15” bit and so on. Placing an octet on a specific location in the long variable can be done simply by multiplying it by 256 and adding its value (to the long variable), which is actually 2 power 8 (8 bits). The third octet which should be put on the 16th bit and therefore should be multiplied by 2 power 16 (65536) and so on. Since we are multiplying by numbers which are base of 2, it is more efficient to use shit left operation. This operation simply moves all the bits of a number to the left “n” times. This is how this operation is written in Java:

x << n

which means: move the bits in “x” “n” times to the left, or in other words: multiply “x” by 2 power “n”. or shortly:

x << n = x * 2 ^ n

where “^” is used as power operator.

For example:

10 << 1 = 10 * 2 ^ 1 = 10 * 2 = 20
10 << 3 = 10 * 2 ^ 3 = 10 * 8 = 80

Converting a numeric IP to a string IP can easily by done by doing the reverse operation, which means dividing and taking the reminder. Of course that instead of using straight division shift right operation can be used, which move all the bits of a number to the right “n” times.

Let’s have a look at the code of the IP conversion functions. It is very simple and pretty much speaks for itself:

package com.bashan.blog;
public class IpUtils {
  public static String longToIp(long longIp) {
    int octet3 = (int) ((longIp >> 24) % 256);
    int octet2 = (int) ((longIp >> 16) % 256);
    int octet1 = (int) ((longIp >> 8) % 256);
    int octet0 = (int) ((longIp) % 256);
    return octet3 + "." + octet2 + "." + octet1 + "." + octet0;
  }
  public static long ipToLong(String ip) {
    String[] octets = ip.split("\\.");
    return (Long.parseLong(octets[0]) << 24) + (Integer.parseInt(octets[1]) << 16) +
        (Integer.parseInt(octets[2]) << 8) + Integer.parseInt(octets[3]);
  }
  public static void main(String[] args) {
    System.out.println("IP 34.56.78.34 as a number: " + ipToLong("34.56.78.34"));
    System.out.println("and converting back to ip: " + longToIp(574115362));
    System.out.println("IP 0.0.0.0 as a number: " + ipToLong("0.0.0.0"));
    System.out.println("and converting back to ip: " + longToIp(0));
    System.out.println("IP 255.255.255.255 as a number: " + ipToLong("255.255.255.255"));
    System.out.println("and converting back to ip: " + longToIp(4294967295L));
  }
}

and this is the test program output:

IP 34.56.78.34 as a number: 574115362
and converting back to ip: 34.56.78.34
IP 0.0.0.0 as a number: 0
and converting back to ip: 0.0.0.0
IP 255.255.255.255 as a number: 4294967295
and converting back to ip: 255.255.255.255