Technical Article

UPC Code with Checksum Function()

,

T-SQL Function
Returns passed 10,11 digit UPC codes with calculated checksum as 12th character.
Based on http://www.mecsw.com/specs/upc_a.html

Create Function fn_UPCWithChecksum (@UPC Varchar(12)) 
RETURNS Varchar(12)
AS
Begin
/*
Based on http://www.mecsw.com/specs/upc_a.html
Returns passed 10,11 digit UPC codes with calculated checksum as 12th character.
If < 11 character long passed code, adds leading zeros.
If = 12 character long passed code, returns passed code unchanged.

Example:
 select dbo.fn_UPCWithChecksum('1200000058')
*/
if len(@UPC) < 11 Select @UPC = Right('00000000000' + @UPC, 11)
If len(@UPC) = 11 Begin
Select @UPC = @UPC + Right(Convert(varchar(2), 10 - Sum(SubString(@UPC, RID, 1) * Case When RID % 2 = 1 Then 3 Else 1 End) % 10), 1)
From (
Select Number as RID 
From Master.dbo.spt_Values 
Where Type = 'P' and Number Between 1 and 11
) A
End
Return @UPC
End

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating