Search This Blog

Wednesday, March 17, 2010

SQL prime numbers

The following function returns the prime numbers of a list of number


CREATE FUNCTION dbo.PrimeNumbers
(
@NumberList nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN

declare @pdv as nvarchar(150)

set @pdv = ''

select @pdv = @pdv + value + ','
from (
select s.value,
case when exists(select 1
from dbo.fn_t_dstring2table(@NumberList, ',') d
where cast(d.value as numeric(18,2)) < cast(s.value as numeric(18,2))
and cast(s.value as numeric(18,2)) % cast(d.value as numeric(18,2)) =0) then 1
else
0
end as divider
from dbo.fn_t_dstring2table(@NumberList, ',') s) as t
where t.divider = 0

return left(@pdv,len(@pdv)-1)

END


Example

select dbo.fn_v_get_PrimeNumbers('2,3,4,6,8,9,10,11,15')

return 2,3,11