Sunday, May 3, 2020

Fun with SQL – Find out numbers where adjacent digit differs by 1

My friend and mentor Madhivanan had posted an interesting scenario on his blog

https://exploresql.com/2020/05/01/fun-with-sql-find-out-numbers-where-adjacent-digit-differs-by-1/

and gave it as a challenge for others

The question was below



I decided to have a try myself on this
Here;s my version of the query for the scenario


--Provide input value 
declare @number int
set @number=105

 ;With numbers
 as
 (
 select 1 as N UNION ALL
 SELECT 1 UNION ALL
 SELECT 1 UNION ALL
 SELECT 1 UNION ALL
 SELECT 1 UNION ALL
 SELECT 1
 ),
 Num_matrix
 AS
 (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 AS Seq
 FROM numbers n1
 cross join numbers n2
 cross join numbers n3
 cross join numbers n4
 )

 select m.Seq
 from
 (
 select Seq 
 from num_matrix 
 where seq < @number 
 )m
 cross join num_matrix n
 where n.seq between 1 and len(m.Seq)
 group by m.Seq 
 having count(*) = 1
 or 
 sum(case when abs(substring(cast(m.seq as varchar(100)),n.seq,1)*1 - nullif(substring(cast(m.seq as varchar(100)),n.seq+1,1),'')*1) = 1
 or  abs(substring(cast(m.seq as varchar(100)),n.seq,1)*1 - nullif(substring(cast(m.seq as varchar(100)),n.seq-1,1),'')*1) = 1 then 0 else 1 end) = 0
 order by m.Seq



And output is the below



Try your version and post back the query in comments

Happy coding!

2 comments:

  1. Very different approach Visakh. Thanks for this post

    Madhivanan

    ReplyDelete
    Replies
    1. Thanks Madhi Sir
      You're an inspiration for lot of professionals like me.
      Keep rocking!

      Delete