declare @beginip varchar(50),@endip varchar(50) select @beginip='103.3.108.0', @endip='103.3.111.255'set nocount on declare @tmpip varchar(50),@p1 int,@p2 int,@p3 int,@p4 intselect @tmpip=@beginipif object_id('tempdb..#iplist') is not null drop table #iplistcreate table #iplist(ip varchar(50))while((cast(parsename(@tmpip,4) as int)*100000+cast(parsename(@tmpip,3) as int)*10000+cast(parsename(@tmpip,2) as int)*1000+cast(parsename(@tmpip,1) as int)) <=(cast(parsename(@endip,4) as int)*100000+cast(parsename(@endip,3) as int)*10000+cast(parsename(@endip,2) as int)*1000+cast(parsename(@endip,1) as int))) begin insert into #iplist(ip) select @tmpip select @p1=parsename(@tmpip,4), @p2=parsename(@tmpip,3), @p3=parsename(@tmpip,2), @p4=parsename(@tmpip,1)+1 if (@p4=256) begin select @p4=1,@p3=@p3+1 end if (@p3=256) begin select @p3=1,@p2=@p2+1 end if (@p2=256) begin select @p2=1,@p1=@p1+1 end select @tmpip=rtrim(@p1)+'.'+rtrim(@p2)+'.'+rtrim(@p3)+'.'+rtrim(@p4) end set nocount off -- 结果 select * from #iplist
SELECT '103.3.'+CONVERT(VARCHAR(10),a.number)+'.'+CONVERT(VARCHAR(10),b.number) AS ip FROM ( SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 108 AND 111 ) A CROSS JOIN ( SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 0 AND 255 ) B
declare @beginip varchar(50),@endip varchar(50)
select @beginip='103.3.108.0',
@endip='103.3.111.255'set nocount on
declare @tmpip varchar(50),@p1 int,@p2 int,@p3 int,@p4 intselect @tmpip=@beginipif object_id('tempdb..#iplist') is not null
drop table #iplistcreate table #iplist(ip varchar(50))while((cast(parsename(@tmpip,4) as int)*100000+cast(parsename(@tmpip,3) as int)*10000+cast(parsename(@tmpip,2) as int)*1000+cast(parsename(@tmpip,1) as int))
<=(cast(parsename(@endip,4) as int)*100000+cast(parsename(@endip,3) as int)*10000+cast(parsename(@endip,2) as int)*1000+cast(parsename(@endip,1) as int)))
begin
insert into #iplist(ip) select @tmpip select @p1=parsename(@tmpip,4),
@p2=parsename(@tmpip,3),
@p3=parsename(@tmpip,2),
@p4=parsename(@tmpip,1)+1
if (@p4=256)
begin
select @p4=1,@p3=@p3+1
end
if (@p3=256)
begin
select @p3=1,@p2=@p2+1
end
if (@p2=256)
begin
select @p2=1,@p1=@p1+1
end
select @tmpip=rtrim(@p1)+'.'+rtrim(@p2)+'.'+rtrim(@p3)+'.'+rtrim(@p4)
end
set nocount off
-- 结果
select * from #iplist
SELECT '103.3.'+CONVERT(VARCHAR(10),a.number)+'.'+CONVERT(VARCHAR(10),b.number) AS ip FROM
(
SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 108 AND 111
) A
CROSS JOIN
(
SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 0 AND 255
) B