0

How to Check Prime Number in TSQL

Emeka OkekeJan 05, 2020 | edited Jan 06, 2020 - by @sqldibia

I see there are people looking for a solution using T-SQL or just SQL to check if a number is a prime number or not.

Now I just realised that I solved this problem out of boredom at my desk during a project in San Francisco back in year 2015.

So here is the T-SQL function I wrote that will check if a number is a prime number or not.



CREATE FUNCTION Primecheck
(@Number BIGINT)
RETURNS VARCHAR(12)
AS BEGIN
DECLARE @RetVal VARCHAR(12)='Prime'

--All even numbers greater than 2 are not prime numbers
IF ((@Number % 2)=0 AND @Number>2)
    RETURN 'Not Prime'
ELSE
--All numbers greater than 3 that are divisible by 3 are not prime numbers
IF ((@Number % 3)=0 AND @Number>3)
    RETURN 'Not Prime'
ELSE
--1 is not a prime number
IF (@Number=1)
    RETURN 'Not Prime'
ELSE
--0 is not a prime number
IF (@Number=0)
    RETURN 'Not Prime'
ELSE
--If the square root of a number is a whole number, then that number is not a prime
IF CHARINDEX('.',CAST(SQRT(@Number) AS VARCHAR(MAX)))=0
    RETURN 'Not Prime'

RETURN @RetVal

END

Prime number function explanation

First of all let us define a prime number.

A prime number is a number that can only be divided by 1 and itself. 
For example 2, 3, 5, 7, 11, 13, 17, 19, 23 are all prime numbers.

So this function takes one input parameter or argument which is the number we want to check if it is prime or not.

First it checks for all even numbers greater than 2 which by default are not prime numbers (refer back to the definition above):



IF ((@Number % 2)=0 AND @Number>2)
    RETURN 'Not Prime'

Next it checks for any number greater than 3 that are divisible by 3 which are not prime numbers by definition:



IF ((@Number % 3)=0 AND @Number>3)
    RETURN 'Not Prime'

Next it checks if the number is 1 which again by definition is not a prime number:



IF (@Number=1)
    RETURN 'Not Prime'

Then it checks if the number is 0 which again by definition is not a prime number since 0 can be divided by all numbers including itself:



IF (@Number=0)
    RETURN 'Not Prime'

Finally if the square root of a number is a whole number, then that number is not a prime (see definition):



IF CHARINDEX('.',CAST(SQRT(@Number) AS VARCHAR(MAX)))=0
    RETURN 'Not Prime'

Note that on each test of the number, the RETURN keyword is invoked, and by so doing terminates the function execution and returns to the caller the specified Not Prime value.

This greatly improved the performance of this prime number checking function, since the rest of the queries will not be executed.

If the number passes through these checks successfully, then it is a prime number by our stated definition.

The function then returns the default value of Prime to the caller.

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia