Calculate the Check Digit for an 11 digit UPC Code

A check digit is a form of redundancy check used for error detection, the decimal equivalent of a binary checksum. It consists of a single digit computed from the other digits in the message.

With a check digit, one can detect simple errors in the input of a series of digits, such as a single mistyped digit, or the permutation of two successive digits.
The final digit of a Universal Product Code( a specific type of barcode, that is widely used in the United States and Canada for tracking trade items in stores.) is a check digit computed as follows:
1. Add the digits (up to but not including the check digit) in the odd-numbered positions (first, third, fifth, etc.) together and multiply by three.
2. Add the digits (up to but not including the check digit) in the even-numbered positions (second, fourth, sixth, etc.) to the result.
3. If the last digit of the result is 0, then the check digit is 0.
4. The check digit will be the smallest number required to round the Sum to the nearest multiple of 10.


For eg: if the UPC barcode for a box of tissues is "036000241457". The last digit is the check digit "7", and if the other numbers are correct then the check digit calculation must produce 7.

1. We add the odd number digits: 0+6+0+2+1+5 = 14
2. Multiply the result by 3: 14 × 3 = 42
3. We add the even number digits: 3+0+0+4+4 = 11
4. We add the two results together: 42 + 11 = 53
5. 60 (the next highest multiple of 10) modulo 53 is 7. Therefore, 7 is the check digit


/*This will return the check digit based on the rules for a 11 digit UPC code */

[code]
CREATE FUNCTION dbo.FuncCheckDigit(@UPCNumber as VarChar(20))

RETURNS Int AS
BEGIN
Declare @Return as Int
Declare @EvenSum as Int
Declare @OddSum as Int
Declare @Total as Int
Declare @CheckDigit as Int

If len(@UPCNumber) = 11
Begin
Set @EvenSum = Cast(Substring(@UPCNumber,1,1) as Int) + Cast(Substring(@UPCNumber,3,1) as Int) + Cast(Substring(@UPCNumber,5,1) as Int) + Cast(Substring(@UPCNumber,7,1) as Int) + Cast(Substring(@UPCNumber,9,1) as Int) + Cast(Substring(@UPCNumber,11,1) as Int)
Set @OddSum = Cast(Substring(@UPCNumber,2,1) as Int) + Cast(Substring(@UPCNumber,4,1) as Int) + Cast(Substring(@UPCNumber,6,1) as Int) + Cast(Substring(@UPCNumber,8,1) as Int) + Cast(Substring(@UPCNumber,10,1) as Int)
Set @Total = @EvenSum * 3 + @OddSum
Set @CheckDigit = 0
While @CheckDigit <=9
Begin
If (Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1))) - (Floor(Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1)))) = 0
Begin
Set @Return = @CheckDigit
End
Set @CheckDigit = @CheckDigit + 1
End
End
Return @Return
END
[/code]