/*
Generate test numbers with random gaps
*/
select
	number
into
	 #test_numbers
from
(
select top 100 percent
	number,
	-- Generate a random number in the range of 0 to 99
	[Mod] = abs(convert(int,convert(varbinary(50),newid())))%100
from
	-- return a list on numbers from 1 to 10000
	dbo.F_TABLE_NUMBER_RANGE(1,10000) a
order by
	number
) a
where
	-- select about a fourth of the numbers 75 to 99
	Mod > 74
order by
	number
go

/*
Find Gaps in numbers by trying to join a number
to the number just before and just after
*/
Select
	a.number,
	Number_Missing_Before	=
	case when b.number is null then a.number-1 else null end,
	Number_Missing_After	=
	case when c.number is null then a.number+1 else null end
into
	#temp
from
	 #test_numbers a
	left join
	 #test_numbers b
	on a.number-1 = b.number
	left join
	 #test_numbers c
	on a.number+1 = c.number
where
	b.number is null	or
	c.number is null
order by
	a.number


/*
Match start of number gap range to end of number gap range
*/
select
	Start_Missing_Range = a.Number_Missing_After,
	End_Missing_Range	=
	(
	select
		min(Number_Missing_Before)
	from
		#temp	bb
	where
		bb.Number_Missing_Before is not null and
		bb.Number_Missing_Before >= a.Number_Missing_After
	)
from
	#temp a
where
	a.Number_Missing_After is not null 
order by
	a.Number_Missing_After



go
drop table #test_numbers
drop table #temp


Sample Output:
Start_Missing_Range End_Missing_Range 
------------------- ----------------- 
3                   4
7                   9
11                  20
22                  23
25                  28
31                  35
37                  45
47                  50
52                  63
65                  71
73                  76
78                  83
...
9976                9980
9984                9989
9991                9991
9994                9994
9996                9997
9999                9999
10001               NULL

(1851 row(s) affected)

set nocount on

declare @s1L tinyint
	,@s2L tinyint
	,@s3L tinyint
	,@s4L tinyint
	,@s1H tinyint
	,@s2H tinyint
	,@s3H tinyint
	,@s4H tinyint
	,@s1 int
	,@s2 int
	,@s3 int
	,@s4 int

--set acceptable Range for each ip segment
select	@s1L = 192	,@s1H = 192
	,@s2L = 168	,@s2H = 168
	,@s3L = 1	,@s3H = 2
	,@s4L = 1	,@s4H = 10

--initialize current segment values with SegmentLowValue
select	@s1 = @s1L
	,@s2 = @s2L
	,@s3 = @s3L
	,@s4 = @s4L

--create a table of numbers 1-255
declare @n table (n tinyint)
insert @n (n)
select n
from	(
	select n1+n2+n3+n4+n5+n6+n7+n8 n
	from	(select 0 n1 union select 1) n1
		,(select 0 n2 union select 2) n2
		,(select 0 n3 union select 4) n3
		,(select 0 n4 union select 8) n4
		,(select 0 n5 union select 16) n5
		,(select 0 n6 union select 32) n6
		,(select 0 n7 union select 64) n7
		,(select 0 n8 union select 128) n8
	) numbers
where	n > 0
and	n < 256 order by 1

--generate some fake IP segments as Used IPs
create table UsedIPs  (seg1 tinyint, seg2 tinyint, seg3 tinyint, seg4 tinyint)
insert UsedIPs values (192,168,1,1)
insert UsedIPs values (192,168,1,2)
insert UsedIPs values (192,168,1,4)
insert UsedIPs values (192,168,1,8)
insert UsedIPs values (192,168,1,10)
insert UsedIPs values (192,168,2,1)
insert UsedIPs values (192,168,2,2)
insert UsedIPs values (192,168,2,4)
insert UsedIPs values (192,168,2,8)
insert UsedIPs values (192,168,2,10)

create table AvailableIPs (seg1 tinyint, seg2 tinyint, seg3 tinyint, seg4 tinyint)

--segment1 loop
while @s1 <= @s1H
begin
	--segment2 loop
	while @s2 <= @s2H
	begin
		
		--segment3 loop
		while @s3 <= @s3H
		begin
			--get all available IPs for current segment1,2,3 values
			insert AvailableIPs (seg1,seg2,seg3,seg4)
			select @s1,@s2,@s3,n.n
			from	@n n
			left join usedIPs u
				on u.seg4 = n.n
				and u.seg1=@s1
				and u.seg2=@s2
				and u.seg3=@s3
			where	u.seg4 is null
			and	n.n >= @s4L
			and	n.n <= @s4H

			set @s3 = @s3+1
		end		
		
		set @s2 = @s2+1
	end --segment2 loop

	set @s1 = @s1+1
end--segment1 loop

print 'UsedIPs'
select	seg1,seg2,seg3,seg4
	,convert(varchar,seg1) + '.'
	+convert(varchar,seg2) + '.'
	+convert(varchar,seg3) + '.'
	+convert(varchar,seg4) as IP
from	usedIPs

print 'AvailableIPs'
select	seg1,seg2,seg3,seg4
	,convert(varchar,seg1) + '.'
	+convert(varchar,seg2) + '.'
	+convert(varchar,seg3) + '.'
	+convert(varchar,seg4) as IP
from AvailableIPs

go
drop table AvailableIPs
drop table UsedIPs

quote:

---

Originally posted by HalaszJ

Is it possible to find a list of missing digits in a table?

Example: I have a table with a field called Octet3, that number will between 1 and 255, I need to know what numbers are not in my table.

---

quote:

---

Originally posted by TG

The last question is waaay different from the first question. 255 possible values is just a little less than power(255,4).

What are the specific ranges you have available? How many of those are currently in use? Do you need a list of every available ip address or do you need process to get 1 unused ip at a time?

Be One with the Optimizer
TG

---

--set acceptable Range for each ip segment
select	@s1L = 192	,@s1H = 192
	,@s2L = 168	,@s2H = 168
	,@s3L = 1	,@s3H = 255
	,@s4L = 1	,@s4H = 255

quote:

---

Originally posted by TG

Did you look at my solution (above)? Run the code (as is) in a QA window to see how it works. You should be able to substitute table name and column names from my table (UsedIPs) with your table/column names. Then set the ranges for each octet in this section of code:

--set acceptable Range for each ip segment
select	@s1L = 192	,@s1H = 192
	,@s2L = 168	,@s2H = 168
	,@s3L = 1	,@s3H = 255
	,@s4L = 1	,@s4H = 255

Be One with the Optimizer
TG

---