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