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

Convert Arabic numbers in SQL 2005

The following function converts arabic numbers (nvarchar) to digital numbers

Alter Function [dbo].[Convert_Arabic_To_Decimal]

(

@String nvarchar(100)

)

Returns numeric(20,6)

as

BEGIN

declare @String2 nvarchar(100)

declare @rtrn as numeric(20,6)

declare @iMode as smallint

declare @bMode as smallint

declare @Digit as numeric(18,6)

set @String2 = @String

set @rtrn = 0

set @iMode = 1

set @bMode = 1

while @String2 <> ''

Begin

if (unicode(left(@string2,1)) >=1632 and unicode(left(@string2,1)) <=1641)

or (unicode(left(@string2,1))=1776)

begin

set @Digit = unicode(left(@string2,1))-1632

if unicode(left(@string2,1))=1776

set @Digit = 0

if @bMode =1

set @rtrn = (@rtrn * power(10,@iMode)) + ( @Digit)

else

begin

set @rtrn = @rtrn + ( @Digit / power(10,@iMode))

set @iMode = @iMode + 1

end

end

else if unicode(left(@string2,1)) = 46

set @bMode = 0

set @String2 = right(@string2,len(@string2)-1)

End

return @rtrn

END

Example:

declare @String as nvarchar(100) set @String = N'٧.۰٧٨٠٧' select [dbo].[Convert_Arabic_To_Decimal](@String)

returns 7.078070

Arabic numbers in SQL 2005

The arabic numbers in SQL 2005 have unicode between 1632 - 1641 where 1632=0 and 1641=9, whoever when converting arabic numeric string to digit number the system usually confuse the NChar(1632) with NChar(1776), both character they look the same

listFind function in MS SQL 2005

The following function locates the position of an element in a delimited list

ALTER FUNCTION [dbo].[listFind]

(

@List nvarchar(max),

@Element nvarchar(150),

@Delimiter nvarchar(10) = ','

)

RETURNS smallint

AS

BEGIN

declare @IndexPos int

declare @CurrentElement nvarchar(150)

declare @I int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return 0

set @IndexPos = charIndex(@Delimiter,@List,0)

set @I = 0

while (@IndexPos <> 0 and @TemList <> '')

Begin

set @I = @I + 1

set @IndexPos = charIndex(@Delimiter,@TemList,0)

if (@IndexPos <> 0)

begin

set @CurrentElement = substring(@temlist,0,@IndexPos)

set @TemList = substring(@TemList,@IndexPos+1,len(@TemList))

end

else

set @CurrentElement = @TemList

if (@CurrentElement = @Element)

return @i

end

if (@CurrentElement = @Element)

return @i

return 0

END

for example

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','dog',',')

return 1

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','bird',',')

return 0

listGetAt functions in SQL 2005

The following list return the element at the X position in a delimited sting list

ALTER FUNCTION [dbo].[listGetAt]

(

@List nvarchar(max), -- delimited list

@ElementPos int, -- the element position

@Delimiter nvarchar(10) = ',', -- the delimiter string

@ReturnOnNotFound nvarchar(100) = null -- the default value if element does not exist

)

RETURNS nvarchar(100)

AS

BEGIN

declare @StrartPos int

declare @IndexPos int

declare @CurrentElement int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return @ReturnOnNotFound

set @StrartPos = 0

set @IndexPos = charIndex(@Delimiter,@List,@StrartPos)

set @CurrentElement = 0

while (@IndexPos <> 0 and @CurrentElement<@ElementPos and @TemList <> '')

Begin

set @CurrentElement = @CurrentElement + 1

set @IndexPos = charIndex(@Delimiter,@TemList,@StrartPos)

if (@IndexPos <> 0 and @CurrentElement<@ElementPos)

set @TemList = substring(@TemList,@IndexPos+1,len(@TemList))

end

if (@CurrentElement<@ElementPos)

return @ReturnOnNotFound

if @IndexPos > 0

return substring(@temlist,0,@IndexPos)

return @temlist

END

For example:

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',2,',','not found')

returns 'cat'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',1,',','not found')

returns 'dog'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',7,',','not found')

returns 'not found'