SQL function to verify israeli ID number

by Carmel Schvartzman

The following SQL function checks an israeli IDENTITY NUMBER for validity, and returns 1 if it's a valid ID, 0 elsewhere:

ALTER function [dbo].[ImutZehut](@id nvarchar(9) ) returns int --- 0=false 1=true
as
begin
if (len(@id) <> 9 or ( isnumeric(@id)=0))
RETURN 0

else
begin
declare @counter int = 0
declare @incNum int
declare @i int = 0

while (@i < len(@id))
begin

set @incNum = cast(substring( @id, @i + 1,1 ) as int) * ((( @i % 2 ) + 1) )

if ( @incNum >= 10)
begin
set @counter = @counter + (@incNum - 9)

end
else set @counter = @counter + @incNum
set @i = @i + 1

end

if ((@counter % 10) = 0) return 1

else return 0

end
return 0

end

Happy programming  :-)