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!

One thought on “How to identify Prime numbers in SQL Server”

  1. This is what I Used…

    DECLARE @cnt INT = 2;
    DECLARE @resp varchar(1000) = ”;

    WHILE @cnt <= 1000
    BEGIN

    Declare @result bit = 1,@i int = 2
    While (@i<@cnt)
    Begin
    if(@cnt % @i = 0)
    Begin
    Set @result = 0
    break
    End
    Set @i += 1
    End

    If @result = 1
    Begin
    DECLARE @cnt_str varchar(50) = Cast(@cnt as varchar(50))

    If @resp = ''
    Begin
    SET @resp = @resp + @cnt_str
    END
    Else
    Begin
    SET @resp = @resp + '&' + @cnt_str
    End;
    End
    SET @cnt = @cnt + 1;

    END;
    Select @resp;

    Like

    Reply

Leave a comment