THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Sergio Govoni

A place where share my experience on SQL Server and related technologies

How to calculate barcode check-digit using T-SQL language

Introduction

In order to scale, it's imperative that companies stay ahead of competition. So how can they identify, store, manage and deliver goods to customers efficiently? The answer is: Barcode! A barcode is the graphical representation of a sequence of numbers and other symbols. The representation is made by lines (bars) and spaces. A barcode typically consists of five parts – and one of these is the check character, also known as the check digit. Handwriting this is a hard work, and is also susceptible to legibility problems. Barcoding dramatically reduces human error, recognition errors and transcription errors.

This article explores the algorithm needed to calculate the check-digit of an UCC/EAN barcode. It compares two possible implementations of the algorithm to calculate the check character for an EAN13 barcode using T-SQL language for SQL Server.

UCC/EAN standard coding

The UCC/EAN standard coding requires that every (well-formed) code ends with a check-digit that will be used by barcode readers to interpret the code properly. The check-digit is a number between zero and nine and it is calculated according to the other digits in the code.

The calculation algorithm requires that every digit in the code is enumerated from right to left as shown in the following picture.

Picture 1 – Digit position table (source GS1 https://www.gs1.org/how-calculate-check-digit-manually)

After you have enumerated each digit of the given code, you need to do the following steps to determine the check character:

  1. Add up the digits in the even positions
  2. Multiply the result of the previous step by three
  3. Add up the digits in the odd positions
  4. Add up the results obtained in steps two and three
  5. Subtract the upper multiple of 10 from the result obtained in step four. For example, if the result of step four is 47, subtract 50 from 47, so the result is 3.

If the result of the four step is a multiple of ten, the check-digit will be equal to zero. Otherwise the check-digit will be the result of the fifth step.

Let's implement the algorithm using T-SQL language

Suppose that your boss asked you to implement a T-SQL object, in an SQL Server database, that it is able to calculate the check-digit for a given EAN13 code. How can you translate the standard algorithm to T-SQL code? ..continue reading full article here.

 

A big "Thank you" to Mia Chang for reviewing this article. 

Published Friday, June 23, 2017 10:00 PM by Sergio Govoni

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement