# Ontario Health Card mod 10 validation

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;


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

• Thanks for the answer but I think it's incorrect. Doing math on characters aside, dividing by 10 is going to result in floating point numbers which is inappropriate for this situation. – Dan Bracuk Jan 20 '18 at 15:35
• You tagged T-SQL and here it's working correctly (automatically casting the string to an int). But for other DBMSes you might have to CAST(Substring(inputString, n,1) AS INT) – dnoeth Jan 20 '18 at 15:56
• @DanBracuk If you're really concerned about the division, you can change them to ROUND((CAST(SUBSTRING(inputString, 1, 1) AS INT) * 2) / 10, 0, 1). That's about as explicit as you can be. The ROUND(value, 0, 1) call will explicitly truncate the fractional portion of the quotient (although as dnoeth said, since we're doing integer division here in T-SQL, we shouldn't get that in the first place). – Bacon Bits Jan 23 '18 at 19:53
• this is so much cleaner than the OP's var table, loops and update spamming solution. Not to talk about the execution plan. – Raul Sebastian Jan 26 '18 at 2:07