Oracle 中 查询IP地址的SQL语句

下面是我在项目（使用的SqlMap）里一个SQL语句关于IP段查询的部分：

<isNotEmpty

prepend="AND"

property="startIP">

CONCAT(LPAD(SUBSTR(CLIENTIP,1,INSTR(CLIENTIP,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(CLIENTIP,INSTR(CLIENTIP,'.',1,1)+1,INSTR(CLIENTIP,'.',1,2)-INSTR(CLIENTIP,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(CLIENTIP,INSTR(CLIENTIP,'.',1,2)+1,INSTR(CLIENTIP,'.',1,3)-INSTR(CLIENTIP,'.',1,2)-1),3,'0'),

LPAD(SUBSTR(CLIENTIP,INSTR(CLIENTIP,'.',1,3)+1,LENGTH(CLIENTIP)),3,'0')

)

)

)

BETWEEN

CONCAT(LPAD(SUBSTR(#startIP#,1,INSTR(#startIP#,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(#startIP#,INSTR(#startIP#,'.',1,1)+1,INSTR(#startIP#,'.',1,2)-INSTR(#startIP#,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(#startIP#,INSTR(#startIP#,'.',1,2)+1,INSTR(#startIP#,'.',1,3)-INSTR(#startIP#,'.',1,2)-1),3,'0'),

LPAD(SUBSTR(#startIP#,INSTR(#startIP#,'.',1,3)+1,LENGTH(#startIP#)),3,'0')

)

)

)

AND

CONCAT(LPAD(SUBSTR(#endIP#,1,INSTR(#endIP#,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(#endIP#,INSTR(#endIP#,'.',1,1)+1,INSTR(#endIP#,'.',1,2)-INSTR(#endIP#,'.',1,1)-1),3,'0'),

CONCAT(LPAD(SUBSTR(#endIP#,INSTR(#endIP#,'.',1,2)+1,INSTR(#endIP#,'.',1,3)-INSTR(#endIP#,'.',1,2)-1),3,'0'),

LPAD(SUBSTR(#endIP#,INSTR(#endIP#,'.',1,3)+1,LENGTH(#endIP#)),3,'0')

)

)

)

</isNotEmpty>

你这种数据结构的话，应该先把IP地址转换成数值，才能用 数值 比较查询。

IP协议规定IP总长度是32位的，所以每组是8位

如果我们把ip地址看成 a.b.c.d，那么转成IP数值就是

d*2的0次方 ＋ c*2的8次方 ＋ b*2的16次方 ＋ a*2的24次方

为了实现IP 字符串转换成数值，要建一个函数。

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数值函数。

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

GO

查询语句中就可以用这个函数了

select address

from IP_table

where f_ip2int(IP_address) beween f_ip2int(IP_start) and f_ip2int(IP_end)

select

LOCATION

from

T_Base_IPAddressInfo

where

'192.168.0.72'

between

startip

and

endip

这样可以查,但是不准确

select

SUBSTR('218.0.6.182',

1,length('218.0.6.182')

-

instr('.',

reverse('218.0.6.182')))

from

(select

*

from

T_Base_IPAddressInfo

where

'218.0.6.182'

between

startip

and

endip)

t

where

startip

=

SUBSTR('218.0.6.182',

1,length('218.0.6.182')

-

instr('.',

reverse('218.0.6.182')))

||

SUBSTR(startip,

instr('.',

reverse(startip)))

需要了解下substr

好instr

的用法

---