将IP地址转换为整数 [英] Convert IP Address to Integer

问题描述

朋友们，

我在检查给定范围内的IP地址时遇到问题.
以下是说明.

1)我在数据库中有两个字段.
i)IP_Address_From
ii)IP_Address_To

2)我获得了用户计算机的当前IP地址，并且
传递到存储过程.

3)此过程调用函数[IP_TO_INT]，该函数
将IP地址转换为整数，并返回''
那个int的答案.

4)我有一个存储过程，该过程检查给定的
给定的IP地址位于IP_Address_From
的字段中 和IP_Address_To放入数据库.

5)问题是存储过程正确，但是
我怀疑函数是否不能将ip转换为整数
正确.

6)下面提到的功能.

Hi friends,

I have a problem with to check IP Address in given range.
below is the description.

1) I have two field in Database.
i) IP_Address_From
ii) IP_Address_To

2) I Get the current IP address of User Computer and
pass into stored procedure.

3) This procedure call a function [IP_TO_INT] which
is convert the ip address into integer, and return ''
that int answer.

4) I have one stored procedure which check that the given
given ip address is reside in to field of IP_Address_From
and IP_Address_To into database.

5) The problem is that the stored procedure is right but
I doubt in the function that it not convert the ip to integer
proper.

6) The function mentioned below.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[IP_To_INT ]
( 
    @IP CHAR(15) 
) 
RETURNS INT 
AS 
BEGIN 
    DECLARE @IntAns INT, 
        @block1 INT, 
        @block2 INT, 
        @block3 INT, 
        @block4 INT, 
        @base INT 
 
    SELECT 
        @block1 = CONVERT(INT, PARSENAME(@IP, 4)), 
        @block2 = CONVERT(INT, PARSENAME(@IP, 3)), 
        @block3 = CONVERT(INT, PARSENAME(@IP, 2)), 
        @block4 = CONVERT(INT, PARSENAME(@IP, 1)) 
 
    IF (@block1 BETWEEN 0 AND 255) 
        AND (@block2 BETWEEN 0 AND 255) 
        AND (@block3 BETWEEN 0 AND 255) 
        AND (@block4 BETWEEN 0 AND 255) 
    BEGIN      
        SELECT @base = CASE 
            WHEN @block1 < 128 THEN 
                (@block1 * 16777216) 
            ELSE 
                -(256 - @block1) * 16777216 
            END 
     
        SET @IntAns = @base +  
            (@block2 * 65536) +  
            (@block3 * 256) + 
            (@block4) 
    END 
    ELSE 
        SET @IntAns = -1 
    RETURN @IntAns 
END

请请为此帮助我，
任何帮助将不胜感激.

谢谢
Mahesh Patel

Please Please Please Help me for this,
Any help will be appreciated.

Thanks
Mahesh Patel

推荐答案

您是否从IP_To_Int获得了负数?您正在遇到整数溢出问题，因此请尝试使用BIGINTS而不是INTS.

Are you getting negative numbers from IP_To_Int? You''re running into integer overflow issues, so try using BIGINTS instead of INTS.

ALTER FUNCTION [dbo].[IP_To_INT ]
( 
    @IP CHAR(15) 
) 
RETURNS BIGINT 
AS 
BEGIN 
    DECLARE @IntAns BIGINT, 
        @block1 BIGINT, 
        @block2 BIGINT, 
        @block3 BIGINT, 
        @block4 BIGINT, 
        @base BIGINT 
 
    SELECT 
        @block1 = CONVERT(BIGINT, PARSENAME(@IP, 4)), 
        @block2 = CONVERT(BIGINT, PARSENAME(@IP, 3)), 
        @block3 = CONVERT(BIGINT, PARSENAME(@IP, 2)), 
        @block4 = CONVERT(BIGINT, PARSENAME(@IP, 1)) 
 
    IF (@block1 BETWEEN 0 AND 255) 
        AND (@block2 BETWEEN 0 AND 255) 
        AND (@block3 BETWEEN 0 AND 255) 
        AND (@block4 BETWEEN 0 AND 255) 
    BEGIN      
        SET @base = CONVERT(BIGINT, @block1 * 16777216)
        SET @IntAns = @base +  
            (@block2 * 65536) +  
            (@block3 * 256) + 
            (@block4) 
    END 
    ELSE 
        SET @IntAns = -1 
    RETURN @IntAns 
END

可能不是有史以来最好的SQL，但是当IP地址变大(即255.255.255.255)时，它将防止出现负数

Might not be the best sql ever, but will prevent negative numbers when the IP address get to be big (i.e. 255.255.255.255)

这篇关于将IP地址转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！