Tutorial :Porting from MySql to T-Sql. Any INET_ATON() equivalent?



Question:

Need to move some code from MySql to TSql. I have a couple of calls to INET_ATON which converts a string which resembles an IPAddress into a number. Is there a T-SQL equivalent?


Solution:1

An abuse of the parsname function:

create function INET_ATON (@addr varchar(15))  returns bigint  with schemabinding  as  begin    return        cast(parsename(@addr, 4) as bigint) * 16777216 +      cast(parsename(@addr, 3) as bigint) * 65536 +      cast(parsename(@addr, 2) as bigint) * 256 +      cast(parsename(@addr, 1) as bigint)  end  

That "short form address" thing is not supported here though.


Solution:2

Here's a function to convert an IP address to a string:

CREATE FUNCTION dbo.IpToString       (@ip_str VarChar(15))  returns BigInt  as      begin      declare @i int      declare @dot_pos int      declare @current_part VarChar(15)      declare @result BigInt        set @result = 0      set @i = 0        while Len(@ip_str) > 0          begin          set @i = @i + 1          set @dot_pos = CharIndex('.', @ip_str)          if @dot_pos > 0              begin              set @current_part = Left(@ip_str, @dot_pos - 1)              set @ip_str = SubString(@ip_str, @dot_pos + 1, 15)              end          else               begin              set @current_part = @ip_str              set @ip_str = ''              end            if Len(@current_part) > 3 Return(Null)          if IsNumeric(@current_part) = 0 Return (Null)          if not cast(@current_part as int) between 0 and 255 Return (Null)          set @result = 256 * @result + Cast(@current_part as BigInt)          end        if @i = 4 Return(@result)        Return(Null)      end  

After creating the function, you can call it like:

select dbo.IpToString('1.2.3.4')  


Solution:3

Little better. It uses int (4b) instead of bigint (8b). Your result should only be four bytes... one per octet:

create function INET_ATON (@ip varchar(15))  returns int  begin      declare @rslt int       -- This first part is a little error checking      -- Looks for three dots and all numbers when not dots       if len(@ip) - len(replace(@ip,'.','')) = 3           AND              isnumeric(replace(@ip,'.','')) = 1      begin       set @rslt = convert(int,              convert(binary(1),convert(tinyint,parsename(@ip, 4)))          +   convert(binary(1),convert(tinyint,parsename(@ip, 3)))          +   convert(binary(1),convert(tinyint,parsename(@ip, 2)))          +   convert(binary(1),convert(tinyint,parsename(@ip, 1)))          )      end      else set @rslt = 0      return @rslt  end;  


Solution:4

Two small improvements.

  1. Written as an in-line table-valued function
  2. Works around the fact that PARSENAME is non-deterministic

Function:

CREATE FUNCTION dbo.IPv4ToInt   (      @ip varchar(15)  )  RETURNS TABLE  WITH SCHEMABINDING  AS  RETURN      SELECT          IPv4Int =          CASE              WHEN LEN(@ip) - LEN(REPLACE(@ip COLLATE Latin1_General_BIN2, '.', '')) = 3              AND @ip COLLATE Latin1_General_BIN2 NOT LIKE '%[^.0-9]%'              AND @ip COLLATE Latin1_General_BIN2 LIKE '[0-9]%.[0-9]%.[0-9]%.[0-9]%'                  THEN                  CONVERT                  (                      integer,                      (                          CONVERT(binary(1), CONVERT(tinyint, SUBSTRING(@ip COLLATE Latin1_General_BIN2, 1, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) - 1)))                          +                          CONVERT(binary(1), CONVERT(tinyint, SUBSTRING(@ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) - (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) - 1)))                          +                          CONVERT(binary(1), CONVERT(tinyint, SUBSTRING(@ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) + 1, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) + 1)) - (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) - 1)))                          +                          CONVERT(binary(1), CONVERT(tinyint, SUBSTRING(@ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) + 1)) + 1, LEN(@ip) - (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, (CHARINDEX('.', @ip COLLATE Latin1_General_BIN2, 1)) + 1)) + 1)))))                      )                  )              ELSE NULL          END;  

Show the properties of the function:

SELECT      IsDeterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IPv4ToInt', N'IF'), 'IsDeterministic'),      IsSystemVerified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IPv4ToInt', N'IF'), 'IsSystemVerified'),      IsPrecise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IPv4ToInt', N'IF'), 'IsPrecise');  

Usage example:

DECLARE @Data TABLE  (      IPv4    varchar(15) NULL  );    INSERT @Data      (IPv4)  VALUES      ('192.168.0.3'),      ('0.0.0.0'),      ('10.0.16.129'),      ('255.255.255.255');    SELECT *   FROM @Data AS d        CROSS APPLY dbo.IPv4ToInt(d.IPv4) AS ipti;  


Solution:5

More a different option than a direct answer to your question (I see the downvotes coming ^^), but you could also consider putting the conversion logic into your software instead of the query. Depending on language and use case this might be even better.

Examples

PHP: ip2long("192.168.1.1");

C/C++: inet_addr("192.168.1.1");

C#

System.Net.IPAddress ip;  long ipn = (System.Net.IPAddress.TryParse("192.168.1.1", out ip))        ? (((long) ip.GetAddressBytes()[0] << 24) | (ip.GetAddressBytes()[1] << 16) |                (ip.GetAddressBytes()[2] <<  8) |  ip.GetAddressBytes()[3])        : 0;  

You could also give it -1, or null (with long? as datatype), or write a method which throws an exception, in case the conversion fails.

Python

reduce(lambda sum, chunk: sum <<8 | chunk, map(int, '192.168.1.1'.split(".")))  

Before you start downvoting: This is just a short example, no error handling here, I know.

Conclusion

Of course it is most of the time nicer to let the dbs do the job, but it really depends, and in case you don' t have a project with millions of requests per second, this might help.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »