Converting barcodes with Addon

A store that sells a large number of magazines had a general level of untidiness in their barcodes. Barcodes had been recorded including the addon in many cases which meant that for each issue change of the magazine the barcode would no longer work.

What are addons?

Addon barcodes are an additional small barcode beside the main barcode. They are generally used for issue based items to indicate the issue number. The main barcode remains the same from issue to issue, but the addon changes. Addons are typically 2 digits long for magazines, although they can be 2 or 5 digits.

Analysis and Solution

A normal magazine barcode is typically 12 or 13 digits long. When stored with the addon in the database they become 14 or 15 digits long.

• As the addons are always 2 digits, it is possible to use SQL to insert additional barcode rows with just the barcode stem, dropping the addon component.
• The barcode scanners in store are already programmed to decode and transmit the addon. However by default they simply include the addon if it is present, which is how the original situation arose. The scanners were reprogrammed to have AIM character decoding which results in the scanner sending additional characters with the barcode.

  	Raw Scanner Output (Original)	AIM Output (New setup)
  Normal Barcode, no addon	123456789876	]E0123456789876
  Barcode with addon	12345678987601	]E0123456789876]E301

SQL to execute

The following SQL commands were used to split the barcodes with addons included and store only the stem part, if it was not already present. Normally direct physical access to the database is discouraged, but in this case the need to do this is probably a one off occurrance and a 90% solution is all that is required. Errors and exceptions can be manually corrected by instore staff.

 insert into barcodes (pid, barcode, manflags) 
 select pid,substring(b.barcode,0,13),1 from barcodes b
 where datalength(barcode)=14
 and substring(b.barcode,0,13) not in (select z.barcode from barcodes z where z.pid=b.pid)
 group by pid,substring(b.barcode,0,13)

 insert into barcodes (pid, barcode, manflags) 
 select pid,substring(b.barcode,0,14),1 from barcodes b
 where datalength(barcode)=15
 and substring(b.barcode,0,14) not in (select z.barcode from barcodes z where z.pid=b.pid)
 group by pid,substring(b.barcode,0,14)