• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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
Asked:
BehrangDBA
  • 3
1 Solution
 
Pawan KumarDatabase 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
)

--

Open in new window


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
)

--

Open in new window


Output
------------------------
PrimeNumbers
-------------------------
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
0
 
Pawan KumarDatabase ExpertCommented:
Function with usage

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

INSERT INTO @Table(PrimeNumbers)
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

Open in new window



usage --

SELECT * FROM dbo.Primes(2,50)

Open in new window


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

--
CREATE Function Primesss
(
	 @StartRange INT
	,@EndRange INT
)
RETURNS @Table TABLE 
(
   PrimeNumbers INT
)
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   
)
INSERT INTO @Table(PrimeNumbers)
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
--

Open in new window



usage --

SELECT * FROM dbo.Primes(2,50)

Open in new window


output

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

PrimeNumbers
15


Hope it helps !!

---------
1
 
Pawan KumarDatabase ExpertCommented:
Hi BehrangDBA,
Did you get a chance to check this?

Regards,
Pawan
0
 
BehrangDBAAuthor Commented:
Thank you very much for your time Pawan.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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