Porting from MySQL to T-Sql. Any Inet_Aton() Equivalent

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

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.

What is the SQL Server equivalent of INET_ATON

Take a look at these stored procedure examples for achieving this

CREATE FUNCTION dbo.ipStringToInt 
( 
    @ip CHAR(15) 
) 
RETURNS INT 
AS 
BEGIN 
    DECLARE @rv INT, 
        @o1 INT, 
        @o2 INT, 
        @o3 INT, 
        @o4 INT, 
        @base INT 

    SELECT 
        @o1 = CONVERT(INT, PARSENAME(@ip, 4)), 
        @o2 = CONVERT(INT, PARSENAME(@ip, 3)), 
        @o3 = CONVERT(INT, PARSENAME(@ip, 2)), 
        @o4 = CONVERT(INT, PARSENAME(@ip, 1)) 

    IF (@o1 BETWEEN 0 AND 255) 
        AND (@o2 BETWEEN 0 AND 255) 
        AND (@o3 BETWEEN 0 AND 255) 
        AND (@o4 BETWEEN 0 AND 255) 
    BEGIN      
        SELECT @base = CASE 
            WHEN @o1 < 128 THEN 
                (@o1 * 16777216) 
            ELSE 
                -(256 - @o1) * 16777216 
            END 

        SET @rv = @base +  
            (@o2 * 65536) +  
            (@o3 * 256) + 
            (@o4) 
    END 
    ELSE 
        SET @rv = -1 
    RETURN @rv 
END

Example usage

INSERT mytable VALUES(dbo.ipStringToInt('1.2.3.4'))

If you want to reverse that and turn an integer into a dotted-quad, try this

CREATE FUNCTION dbo.ipIntToString 
( 
    @ip bigINT 
) 
RETURNS CHAR(15) 
AS 
BEGIN 
    DECLARE @o1 bigINT, 
        @o2 bigINT, 
        @o3 bigINT, 
        @o4 bigINT 

    IF ABS(@ip) > 4294967295 
        RETURN '255.255.255.255' 

    SET @o1 = @ip / 16777216 

    IF @o1 = 0 
        SELECT @o1 = 255, @ip = @ip + 16777216 

    ELSE IF @o1 < 0 
    BEGIN 
        IF @ip % 16777216 = 0 
            SET @o1 = @o1 + 256 
        ELSE 
        BEGIN 
            SET @o1 = @o1 + 255 
            IF @o1 = 128 
                SET @ip = @ip + 2147483648 
            ELSE 
                SET @ip = @ip + (16777216 * (256 - @o1)) 
        END 
    END 
    ELSE 
    BEGIN 
        SET @ip = @ip - (16777216 * @o1) 
    END 

    SET @ip = @ip % 16777216 
    SET @o2 = @ip / 65536 
    SET @ip = @ip % 65536 
    SET @o3 = @ip / 256 
    SET @ip = @ip % 256 
    SET @o4 = @ip 

    RETURN 
        CONVERT(VARCHAR(4), @o1) + '.' + 
        CONVERT(VARCHAR(4), @o2) + '.' + 
        CONVERT(VARCHAR(4), @o3) + '.' + 
        CONVERT(VARCHAR(4), @o4) 
END

Sql LIKE a range of numbers

Don't store IP addresses as strings. Store them as integers.

Use the MySQL function INET_ATON() to convert an IP address string to a number, and INET_NTOA() to convert back. Once this conversion has been performed, the numbers corresponding to IPs will be sequential, so you can use conditions like IPaddress BETWEEN INET_ATON('100.45.0.0') AND INET_ATON('100.150.255.255') in your query.

(Your programming language may have similar functions as well; for instance, PHP has ip2long() and long2ip(). The same principles will work there.)

alternative MySQL code for INET6_ATON

I found the solution.

With an UPDATE query we can fetch the original value from the database, cast it to int again, hex and unhex it (can this be further shortened?) and we get the right binary value in the database.

UPDATE table SET ip = UNHEX(HEX(CAST(ip AS UNSIGNED)))

Translating a PostgreSQL operator to a MySQL equivalent?

That operator is known 'Is contained within or or equals'

when ip <<= inet('128.218.0.0/16') then 1

so what you are doing here is trying to find out if your ip belongs to the 128.218.0.0/16 network. That is whether the IP address is within 128.218.0.0 to 128.218.0.255

So you can do this with a numeric comparision. But the question is why are you moving from a feature rich, more powerfull, faster database to a one that does not have so many features, slower and in some cases down right broken?

The above is roughly equivalent to

when ip >= inet_aton('128.218.0.0') AND ip <= INET_ATON('128.218.0.255) then 1

Similarly

when ip >= inet_aton('10.0.0.0') AND ip <= INET_ATON('10.0.255.255) then 1

Is ip2long() in PHP equal to INET_ATON() function in MySQL?

They are almost exactly the same. ip2long sometimes returns a negative value because PHP uses signed numbers for valuation, while MySQL uses unsigned.

Both are evaluated as x*(2^24) + y*(2^16) + z*(2^8) + w*(2^0), but in PHP, due to the long being signed, will show negative values for certain IP addresses.

For signed long, the range is 
(2^31) - 1 = −2,147,483,648 to +2,147,483,647

So, addresses while translate to over +2,147,483,647 will wrap around and give negative values.

ip2long("254.254.254.254"); // -16843010

This link describes this in detail.

---

Related Topics