Ontario Health Card mod 10 validation

Question

An example of the business rules are here.

Does this code implement those rules correctly? Note that you will have to derive the rules from the example.

/*
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;

Answer 1

This is based on the Luhn-algorithm, see this answer.

Those solutions are generic, i.e. for any input length, but for a specific length string I prefer a brute-force approach, no variables/loops/etc., just cut&paste&modify:

select -- luhn check digit
   (10-
     ( -- sum of all digits after doubling the odd digits
        Substring(inputString, 1,1)*2 % 10
      + Substring(inputString, 1,1)*2 / 10
      + Substring(inputString, 2,1)
      + Substring(inputString, 3,1)*2 % 10
      + Substring(inputString, 3,1)*2 / 10
      + Substring(inputString, 4,1)
      + Substring(inputString, 5,1)*2 % 10
      + Substring(inputString, 5,1)*2 / 10
      + Substring(inputString, 6,1)
      + Substring(inputString, 7,1)*2 % 10
      + Substring(inputString, 7,1)*2 / 10
      + Substring(inputString, 8,1)
      + Substring(inputString, 9,1)*2 % 10
      + Substring(inputString, 9,1)*2 / 10
     ) % 10 -- subtract the last digit
   ) % 10   -- in case last digit is a zero: 10 -> 0

Put it in a CASE to get 0/1:

case when previous calculation = Substring(inputString, 10,1)
     then 1 
     else 0
end