Today, I have come through a post by one of my good friends and one of the most famous SQL Server experts in India – Madhivanan.

The question was : “Given a number N, write a code to print all positive numbers less than N in which all adjacent digits differ by 1” Here is my attempt to solve this using SQL Server T-SQL

--Provide input value 
declare @number int
set @number=105
 
--Create a temporary table to store numbers
Drop table if exists #temp
create table #temp (number int)

;with ctebuilder as(
select
    row_number () over (order by (select null)) as sno 
from
    sys.sysobjects as t1 cross join sys.sysobjects as t2 ) 
Insert into #temp(number) Select sno From ctebuilder where sno<=@number

;with cte as(
select
    t1.number number, 
    cast(substring(cast(t1.number as varchar(20)),t2.number,1) as int) as number2, 
	row_number () over(partition by t1.number order by (select null) asc) Rn
from #temp as t1 cross join #temp as t2
where t2.number <= len(t1.number) and t1.number <=@number
)
Select number From(
 Select * ,LAG(number2,1) OVER (partition by number
		ORDER BY (select NULL) asc
	) previous_val From cte
	) A group by number 
having count(case when previous_Val - number2 in (1,-1) then 1 Else NULL End )  
= count(number2)-1

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!