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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[bigint_to_ip](@ip bigint)RETURNS varchar(15)ASBEGINDECLARE @octet0 varchar(3)DECLARE @octet1 varchar(3)DECLARE @octet2 varchar(3)DECLARE @octet3 varchar(3)SET @octet3 = (@ip / power(2, 24)) % 256SET @octet2 = (@ip / power(2, 16)) % 256SET @octet1 = (@ip / power(2, 8)) % 256SET @octet0 = @ip % 256RETURN @octet3 + '.' + @octet2 + '.' + @octet1 + '.' + @octet0END
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: thisPARSENAME(@ObjectName, 3) -- Return: isPARSENAME(@ObjectName, 2) -- Return: myPARSENAME(@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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[ip_to_bigint](@ip varchar(15))RETURNS bigintASBEGINDECLARE @octet0 bigintDECLARE @octet1 bigintDECLARE @octet2 bigintDECLARE @octet3 bigintSET @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) + @octet0END