表A中 有列 IP 保存的是ip地址 格式如下
IP
198.168.0.1
192.168.0.5
192.168.0.15
192.168.0.215
192.168.1.5
192.168.1.25
192.168.1.23
192.168.1.155
192.168.2.5
192.168.2.53
192.168.2.223
//////////
请问我想查询某个IP段之间(如192.168.0.1-192.168.0.255 是一个IP段 而 198.168.1.1-192.168.1.255 是另一个IP段)都有那些IP怎么写SQL语句?假若我要查询 192.168.0.1-192.168.0.255 这个IP断那么就应该市
IP
198.168.0.1
192.168.0.5
192.168.0.15
192.168.0.215
IP
198.168.0.1
192.168.0.5
192.168.0.15
192.168.0.215
192.168.1.5
192.168.1.25
192.168.1.23
192.168.1.155
192.168.2.5
192.168.2.53
192.168.2.223
//////////
请问我想查询某个IP段之间(如192.168.0.1-192.168.0.255 是一个IP段 而 198.168.1.1-192.168.1.255 是另一个IP段)都有那些IP怎么写SQL语句?假若我要查询 192.168.0.1-192.168.0.255 这个IP断那么就应该市
IP
198.168.0.1
192.168.0.5
192.168.0.15
192.168.0.215
where ip like '192.168.0.%'
insert A select '198.168.0.1'
union all select '192.168.0.5'
union all select '192.168.0.15'
union all select '192.168.0.215'
union all select '192.168.1.5'
union all select '192.168.1.25'
union all select '192.168.1.23'
union all select '192.168.1.155'
union all select '192.168.2.5'
union all select '192.168.2.53'
union all select '192.168.2.223'select * from A
where ip like '192.168.0.%'--result
ip
--------------------
192.168.0.5
192.168.0.15
192.168.0.215(3 row(s) affected)
drop function [dbo].[f_IP2Int]
GO--1. 字符串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
/*===========================================================*/
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--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.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
GO
显示时把数值转换成IP
这个字段是个varchar32的
真郁闷
insert A select '198.168.0.1'
union all select '192.168.0.5'
union all select '192.168.0.15'
union all select '192.168.0.215'
union all select '192.168.1.5'
union all select '192.168.1.25'
union all select '192.168.1.23'
union all select '192.168.1.155'
union all select '192.168.2.5'
union all select '192.168.2.53'
union all select '192.168.2.223'select * from A
where ip like '192.168.0.%'
and cast(substring(ip,11,3) as tinyint) between 56 and 255
CREATE FUNCTION [dbo].[FunCmpIp] (@startip varchar(40))
RETURNS decimal
AS
BEGIN
declare @i int --标记ip地址中的"."符号位置
declare @pw int --2进制的幂值
declare @ipvalue decimal--计算ip的二进制值
set @ipvalue=0
set @pw=3
--set @startip='192.168.0.1'
set @i=charindex('.',@startip)
while @i>=1
begin
--insert @temp values(replace(left(@startip,@i-1),' ',''))
set @ipvalue=@ipvalue+CAST(replace(left(@startip,@i-1),' ','') as decimal)*POWER(256,@pw)
set @startip=substring(@startip,@i+1,len(@startip)-@i)
set @i=charindex('.',@startip)
set @pw=@pw-1
end
set @ipvalue=@ipvalue+CAST(@startip as int)
return @ipvalue
END;
加入二decimal字段,startnumip,endnumip,
insert into table (startnumip,endnumip)
select [dbo].[FunCmpIp](startip),[dbo].[FunCmpIp](startip) from table
where [dbo].[FunCmpIp](ip) between startnumip and endnumip