Tag: Fun with T SQL

FUN with SQL, SQL

How to identify Prime numbers in SQL Server
While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment. 
declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
    SELECT 2
    UNION ALL
    SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n 
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);

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

FUN with SQL, SCRIPT GALLERY, SQL

Fun with SQL – Find out numbers where adjacent digit differs by 1
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!