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!

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