SQL查询语句的问题？

[dume2007]

我有一个IP数据库：

startip endip address
24.69.255.0 24.71.169.255 ..
24.71.170.0 24.72.92.255 ..
24.72.93.0 24.72.93.255 ..


用sql如何查询某个IP在哪两个IP段之间？或者有什么合理的查询方式都可以。

[vbman2003]

select *
form tb
where startip<='你要查询的IP' and endip >='你要查询的IP'

[lxq19851204]

用函数直接将24.69.255.0形式的IP按256进制转为数字，然后进行between(或者"<"AND">")。

[lxq19851204]

--> liangCK小梁 于2008-09-20
--> 生成测试数据: [tableip]
if object_id('[tableip]') is not null drop table [tableip]
create table [tableip] (id int,startip varchar(15),endip varchar(15),address nvarchar(6))
insert into [tableip]
select 1,'024.089.048.000','024.089.063.255','ARIN' union all
select 2,'024.089.064.000','024.089.127.255','加拿大' union all
select 3,'024.089.128.000','024.089.191.255','美国' union all
select 4,'024.089.192.000','024.089.255.255','加拿大' union all
select 5,'024.090.000.000','024.100.063.255','美国' union all
select 6,'024.100.064.000','024.103.255.255','ARIN' union all
select 7,'024.104.000.000','024.104.159.255','美国' union all
select 8,'024.104.160.000','024.104.255.255','ARIN'

--SQL查询如下:

go

--这个函数不用改.直接搬着来用.
CREATE FUNCTION dbo.f_IP2Int(
@ip varchar(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

--要查的IP
DECLARE @ip VARCHAR(15);

SET @ip='24.93.121.22';

SELECT * 
FROM [tableip]
WHERE dbo.f_IP2Int(@ip)>=dbo.f_IP2Int(startip) 
   and dbo.f_IP2Int(@ip)<=dbo.f_IP2Int(endip) ;
   
GO

--删除测试表,函数.
DROP TABLE [tableip]
DROP FUNCTION dbo.f_IP2Int

id          startip         endip           address
----------- --------------- --------------- -------
5           024.090.000.000 024.100.063.255 美国

(1 行受影响)

[贝隆]

使用BETWEEN语句
select * Form TB WHERE FD_IP BETWEEN('192.168.1.100' and '192.168.1.200')

[vbman2003]

恩，1L错了，字符串比较结果不正确的，算我没说.....项2L