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?

Answer 1

give an example of function input and output

Answer 2

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.

Answer 3

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')

Answer 4

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;