sql server ip地址between 如何查询
0
[已解决问题]
解决于 2008-12-25 11:32
sql server 2个ip地址段如何between查询
和ip小于或者大于查询
最佳答案
0
你好，我找了下，这是csdn上以前一个人的回复：
－－引用开始－－－
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO
/*--字符型 IP 地址转换成数字 IP
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select dbo.f_IP2Int('192.168.0.11')
select dbo.f_IP2Int('12.168.0.1')
--*/
CREATE FUNCTION f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)A
RETURN(@re)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Int2IP]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_Int2IP]
GO
/*--数字 IP 转换成格式化 IP 地址
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select dbo.f_Int2IP(3232235531)
select dbo.f_Int2IP(212336641)
--*/
CREATE FUNCTION f_Int2IP(
@IP bigint
)RETURNS varchar(15)
AS
BEGIN
DECLARE @re varchar(15)
SET @re=''
SELECT @re=@re+'.'+CAST(@IP/ID as varchar)
,@IP=@IP%ID
from(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(STUFF(@re,1,1,''))
END
上面是IP地址的字符形式与数据形式之间的相互转换函数.
IP地址被保存前应该转换为数字,仅在显示时转换为字符,处理时应该统一用数字处理.
－－－－引用结束－－－－
其他回答(1)
0
Code
create function dbo.fn_IP(@ip varchar(15))
returns bigint
begin
return parsename(@ip,4)*cast(16777216 as bigint)+parsename(@ip,3)*65536+parsename(@ip,2)*256+parsename(@ip,1)
end
declare @tb table (id int,ip varchar(15))
insert @tb select 1,'10.210.128.207'
UNION ALL select 2,'10.210.128.206'
UNION ALL select 3,'10.210.128.205'
UNION ALL select 4,'10.210.128.204'
UNION ALL select 5,'10.210.128.203'
UNION ALL select 6,'10.210.128.202'
UNION ALL select 7,'10.210.128.201'
select id,ip,dbo.fn_IP(ip) from @tb
where dbo.fn_IP(ip) between dbo.fn_IP('10.210.128.203') and dbo.fn_IP('10.210.128.205')
