Create a range of numbers excluding numbers

Transact-SQL
1

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
image
Any Idea, thanks!

2

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
3

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 )
:slight_smile: :slight_smile:

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

image

4

This is it, Thanks a lot! :wink:

5

Using a recursive CTE with a larger set is very inefficient. https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes