问安大略省健康卡mod 10验证

EN

/*
test number from url
9876543217

other valid numbers
5322369835
7089771195
8108876957
4395667779
6983806917

not valid numbers
2790412845

5762696912
*/

declare @inputString as varchar(10) = '2790412845'
, @mathResult as tinyint
, @digitNumber as tinyint = 1
, @isValid as bit = 1
, @checkDigitPosition as tinyint
, @tenthDigit as tinyint;

declare @digits as table (
DigitNumber tinyint
, Digit tinyint
, NewDigit tinyint
);

while @digitNumber <= 10
begin
insert into @digits (
DigitNumber
, Digit
)
select @digitNumber
, cast(substring(@inputString, @digitNumber, 1) as tinyint) digit;

set @digitNumber = @digitNumber + 1;
end
/*
Double 1st, 3rd, 5th, 7th and 9th Digits and take the sum of their digits
*/
-- even numbered digits
update @digits
set NewDigit = Digit
where DigitNumber % 2 = 0;

-- odd numbered digits
update @digits
set NewDigit = cast(left(cast((Digit * 2) as char(2)), 1) as tinyint) 
         + cast(right(cast((Digit * 2) as char(2)), 1) as tinyint)
where DigitNumber % 2 = 1;

--add the 1st nine new digits together and take 2nd digit (mod 10)
select @mathResult = result 
from 
(select sum(NewDigit) % 10 result
from @digits
where DigitNumber <= 9) temp;

select @mathResult w;
select * from @digits;
-- Subtract The Unit Position From Ten

set @checkDigitPosition = 10 - @mathResult;
-- compare this number to the 10th digit
select @tenthDigit = Digit
from 
(select Digit
from @digits
where DigitNumber = 10) temp;

select @checkDigitPosition p
, @tenthDigit t
, case when @tenthDigit = @checkDigitPosition then 1 else 0 end isValid;