x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 78

# Writing a function to counts the number of primes in the range [1-N].

Hi everyone,
How can I  write a function that counts the number of primes in the range [1-N].
(A natural number is called a prime, a prime number or just prime if it has exactly two distinct divisors. Therefore, 1 is not prime number, since it has only one divisor, namely 1.)
0
BehrangDBA
• 3
1 Solution

Database ExpertCommented:
Try this

``````--

DECLARE @StartRange AS INT = 2
DECLARE @EndRange AS INT = 50

SELECT COUNT (DISTINCT Number) PrimeNumbers
FROM
MASTER..SPT_VALUES num
WHERE
Number >= @StartRange AND Number <= @EndRange AND
NOT EXISTS
(
SELECT 1 FROM MASTER..SPT_VALUES AS num1 WHERE num1.Number > 1
AND  num1.Number < num.Number
AND  num.Number % num1.Number = 0
)

--
``````

if you list of primes use below

``````--

DECLARE @StartRange AS INT = 2
DECLARE @EndRange AS INT = 50

SELECT DISTINCT Number PrimeNumbers
FROM
MASTER..SPT_VALUES num
WHERE
Number >= @StartRange AND Number <= @EndRange AND
NOT EXISTS
(
SELECT 1 FROM MASTER..SPT_VALUES AS num1 WHERE num1.Number > 1
AND  num1.Number < num.Number
AND  num.Number % num1.Number = 0
)

--
``````

Output
------------------------
-------------------------
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
0

Database ExpertCommented:
Function with usage

``````CREATE Function Primes
(
@StartRange INT
,@EndRange INT
)
RETURNS @Table TABLE
(
)
AS
BEGIN

SELECT COUNT (DISTINCT Number) PrimeNumbers
FROM
MASTER..SPT_VALUES num
WHERE
Number >= @StartRange AND Number <= @EndRange AND
NOT EXISTS
(
SELECT 1 FROM MASTER..SPT_VALUES AS num1 WHERE num1.Number > 1
AND  num1.Number < num.Number
AND  num.Number % num1.Number = 0
)

RETURN

END
``````

usage --

``````SELECT * FROM dbo.Primes(2,50)
``````

If you dont want to Use Master..Spt_Values (System Table) use below

``````--
CREATE Function Primesss
(
@StartRange INT
,@EndRange INT
)
RETURNS @Table TABLE
(
)
AS
BEGIN

WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3
)
SELECT COUNT (DISTINCT Number) PrimeNumbers
FROM
Series num
WHERE
Number >= @StartRange AND Number <= @EndRange AND
NOT EXISTS
(
SELECT 1 FROM Series AS num1 WHERE num1.Number > 1
AND  num1.Number < num.Number
AND  num.Number % num1.Number = 0
)

RETURN

END
--
``````

usage --

``````SELECT * FROM dbo.Primes(2,50)
``````

output

---------------

15

Hope it helps !!

---------
1

Database ExpertCommented:
Hi BehrangDBA,
Did you get a chance to check this?

Regards,
Pawan
0

Author Commented:
Thank you very much for your time Pawan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
Tackle projects and never again get stuck behind a technical roadblock.