Print Prime Numbers in SQL

 

Problem

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

For example, the output for all prime numbers <= 10 would be

2&3&5&7

DECLARE i NUMBER(3); j NUMBER(3); BEGIN dbms_output.Put_line('The prime numbers are:'); dbms_output.new_line; i := 2; LOOP j := 2; LOOP EXIT WHEN( ( MOD(i, j) = 0 ) OR ( j = i ) ); j := j + 1; END LOOP; IF( j = i )THEN dbms_output.Put(i||' '); END IF; i := i + 1; exit WHEN i = 50; END LOOP; dbms_output.new_line; END; /

Enter your query here.

Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.

*/

DECLARE @Output AS VARCHAR(MAX) = '';

WITH digit(d)

AS

(

SELECT 0 AS d

UNION ALL

SELECT d+1 AS d FROM digit WHERE d < 9

)

SELECT

@Output += CAST(a.Number AS VARCHAR(3)) + '&'

FROM (

SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a

CROSS JOIN digit b

CROSS JOIN digit c

) a

LEFT JOIN (

SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a

CROSS JOIN digit b

CROSS JOIN digit c

) b ON SQRT(a.Number) >= b.Number AND b.Number > 1

WHERE a.Number > 1

GROUP BY a.Number

HAVING ISNULL(SUM(CASE WHEN a.Number % b.Number = 0 THEN 1 ELSE 0 END),0) = 0

ORDER BY a.Number

PRINT SUBSTRING(@Output,1,LEN(@Output)-1)

;