What is the MSSQL equivalent of INET_ATON

Question

As the question says what is the MSSQL equivalent of INET_ATON from mySql. The reason i need this is because i imported a IP data base from http://ipinfodb.com/ip%5Fdatabase.php into MSSQL 2005 and would like to query the table now. But based on their examples i am not sure how to do that.

INET_ATON(expr)

Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.

mysql> SELECT INET_ATON('209.207.224.40');
        -> 3520061480

Answer 1

There is no built in function to do this in MSSQL, however the formula for converting A.B.C.D to an IP Number is:

IP Number = A x (256*256*256) + B x (256*256) + C x 256 + D

I may have the sql functions written somewhere to do this, ill have a look.

Answer 2

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