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]

## No comments:

## Post a Comment