什么是INET_ATON的SQL Server等效项 [英] What is the SQL Server equivalent of INET_ATON

问题描述

如问题所述，mySql中与INET_ATON等效的SQL Server是什么.我需要这个的原因是因为我从 http://ipinfodb.com/ip_database.php 到SQL Server 2005中，现在想查询该表.但是根据他们的例子，我不确定该怎么做.

As the question says what is the SQL Server equivalent of INET_ATON from mySql. The reason I need this is because i imported a IP data base from http://ipinfodb.com/ip_database.php into SQL Server 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)

给出点分四进制表示 一个网络地址作为一个字符串， 返回一个整数，该整数表示 地址的数值. 地址可以是4字节或8字节 地址.

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

推荐答案

看看这些示例用法

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

这篇关于什么是INET_ATON的SQL Server等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！