博问上的一个问题

sql server 2个ip地址段如何between查询

和ip小于或者大于查询

提问者：Quert - 初学一级

 

 

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

 

 

 

 

ip地址转数值

作者:nz.perfectaction 日期:2008-08-19

字体大小: 小 中 大

 

ASP/Visual Basic代码

Function ChangeIP(ip)
    strIP=Split(ip,".")
    ChangeIP=int(strIP(0))*16777216 + int(strIP(1)) *65536 + int(strIP(2))*256 +int(strip(3))
End Function

SQL代码

create function dbo.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

 

SQL代码

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