将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屋!