# Create a range of numbers excluding numbers

Hi,

Let's say I want to generate a range of numbers knowing the start and the length.
@Start = 1482
@length = 10
@excludeNumbers = '87,88,89'

My result should exclude numbers ending with the list of exclusion Any Idea, thanks!

You can do a lot of ways, but this is one.

``````declare @start int = 1480
declare @length int = 10
declare @exclude varchar(20) = '80,83'
;WITH Nums(Number) AS
(SELECT @start AS Number
UNION ALL
SELECT Number+1 FROM Nums where Number<@start + @length -1
)
select *

from Nums a
left join (select * from string_split(@exclude,',') ) b
on convert(varchar(10),a.Number) like '%' +  b.value
where b.value is null``````
1 Like

hi

i have done this in a different way ...please correct me if i am missing anything
it may be a better or worse solution ( its not a dynamic solution )  ``````declare @Start int = 1482
declare @length int = 10
declare @excludeNumbers varchar(20) = '87,88,89'

; with cte as
(
select @Start as N
union all
select N+1 from cte where N+1 < @Start+@length+3
)
select * from cte
where (N%100) not in (87,88,89)
go
`````` This is it, Thanks a lot! Using a recursive CTE with a larger set is very inefficient. https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes