T-SQL Israeli CardID Checker / ת.ז. ישראלית
T-SQL Israeli CardID Checker / ת.ז. ישראלית
Once in a while I come across code that I didn’t fins in the internet … mainly because it’s for local use ;)
This is one of these times :)
This code is the T-SQL version of the algorithm that checks the given Israeli CardID , and adds a leading Zero , if need one ( like in my personal case ) , and calculates the “Check Number” ( SIFRAT BIKORET ) , if it’s missing.
I needed this for an SSIS Package that loaded monthly information needed for my SharePoint Content ;)
BTW , there might be a better way to code this , but i’m no T-SQL expert :)
/*
Algorithm From
http://halemo.net/info/idcard/index.html
*/
ALTER FUNCTION [dbo].[fn_FixUpCardID]
(
@CardID VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CardID_1 INT
DECLARE @CardID_2 INT
DECLARE @CardID_3 INT
DECLARE @CardID_4 INT
DECLARE @CardID_5 INT
DECLARE @CardID_6 INT
DECLARE @CardID_7 INT
DECLARE @CardID_8 INT
DECLARE @CardID_9 INT
DECLARE @CardID_1_CalcVal INT
DECLARE @CardID_2_CalcVal INT
DECLARE @CardID_3_CalcVal INT
DECLARE @CardID_4_CalcVal INT
DECLARE @CardID_5_CalcVal INT
DECLARE @CardID_6_CalcVal INT
DECLARE @CardID_7_CalcVal INT
DECLARE @CardID_8_CalcVal INT
DECLARE @CardID_9_CalcVal INT
DECLARE @CardID_TotalValue INT
DECLARE @CardID_Decimal INT
IF (LEN(@CardID) = 7)
BEGIN
SET @CardID = '0' + @CardID;
END
SET @CardID_1 = CAST(SUBSTRING(@CardID,1,1) AS INT)
SET @CardID_2 = CAST(SUBSTRING(@CardID,2,1) AS INT)
SET @CardID_3 = CAST(SUBSTRING(@CardID,3,1) AS INT)
SET @CardID_4 = CAST(SUBSTRING(@CardID,4,1) AS INT)
SET @CardID_5 = CAST(SUBSTRING(@CardID,5,1) AS INT)
SET @CardID_6 = CAST(SUBSTRING(@CardID,6,1) AS INT)
SET @CardID_7 = CAST(SUBSTRING(@CardID,7,1) AS INT)
SET @CardID_8 = CAST(SUBSTRING(@CardID,8,1) AS INT)
if (LEN(@CardID) = 9)
BEGIN
SET @CardID_9 = CAST(SUBSTRING(@CardID,9,1) AS INT)
END
ELSE
BEGIN
SET @CardID_9 = -1
END
SET @CardID_1_CalcVal = (@CardID_1 * 1) / 10 + (@CardID_1 * 1) % 10
SET @CardID_2_CalcVal = (@CardID_2 * 2) / 10 + (@CardID_2 * 2) % 10
SET @CardID_3_CalcVal = (@CardID_3 * 1) / 10 + (@CardID_3 * 1) % 10
SET @CardID_4_CalcVal = (@CardID_4 * 2) / 10 + (@CardID_4 * 2) % 10
SET @CardID_5_CalcVal = (@CardID_5 * 1) / 10 + (@CardID_5 * 1) % 10
SET @CardID_6_CalcVal = (@CardID_6 * 2) / 10 + (@CardID_6 * 2) % 10
SET @CardID_7_CalcVal = (@CardID_7 * 1) / 10 + (@CardID_7 * 1) % 10
SET @CardID_8_CalcVal = (@CardID_8 * 2) / 10 + (@CardID_8 * 2) % 10
SET @CardID_TotalValue = @CardID_1_CalcVal + @CardID_2_CalcVal +
@CardID_3_CalcVal + @CardID_4_CalcVal +
@CardID_5_CalcVal + @CardID_6_CalcVal +
@CardID_7_CalcVal + @CardID_8_CalcVal
IF (@CardID_9 > -1)
BEGIN
SET @CardID_9_CalcVal = (@CardID_9 * 1) / 10 + (@CardID_9 * 1) % 10
IF (((@CardID_TotalValue + @CardID_9_CalcVal) % 10) != 0)
SET @CardID = ''
END
ELSE
BEGIN
if ((@CardID_TotalValue % 10) = 0)
BEGIN
SET @CardID = @CardID + '0'
END
ELSE
BEGIN
SET @CardID_9 = (((@CardID_TotalValue / 10) + 1) * 10) - @CardID_TotalValue
SET @CardID = @CardID + CAST(@CardID_9 AS CHAR)
END
-- SET @CardID = cast((((@CardID_TotalValue % 10) + 1) * 10) as varchar)
END
RETURN @CardID
END