Code 128 C - SQL Function

The Code 128 barcode is a high-density linear symbology that encodes text, numbers, numerous functions and the entire 128 ASCII character set (from ASCII 0 to ASCII 128.) It is commonly used for several implementations; and is also referred to as ISBT-128, GS1-128, UCC-128, EAN-128 and USS Code 128.

Code 128 contains 106 different printed barcode patterns. Each printed barcode may have one of three different meanings depending upon which of the character sets are being used, with the availability of three different Code 128 start characters to program the initial character set. Functions are also provided in the barcode symbology to switch between character sets and encode Application Identifiers. The Code 128 barcode may be complex to use because of the different character sets, which is the primary reason IDAutomation provides the Code 128 auto function, "Code128( )", in several of the Font Tools, Components and Applications.
The complete Code 128 barcode consists of a start character, data digits, a modulo 103 check digit and a stop character.

Many developers are searching a way of how to correctly encode the string using SQL.
I had a need to dispaly a 10 characters long string (containing numbers only) as a barcode, this is why I had first to encode it correctly in Code 128 C format.


You can find more details about IDAutomation on this page: http://www.idautomation.com/code128faq.html

I will provide below a function which I have implementing for this:

-- Description: This function will encode the given string to 128c formatCREATE FUNCTION [dbo].[CodeTo128C] (@stringToEncode VARCHAR(10))RETURNS VARCHAR(8) AS
BEGIN

   DECLARE @encodedString AS VARCHAR(8),
           @stringLength AS INT
          
   SET @encodedString = ''
   SET @stringLength = LEN(@stringToEncode)

   IF @stringLength > 0
   BEGIN
       -- Add the start Code
       SET @encodedString = CHAR(205)

       DECLARE @checkSumTotal AS INT,
               @iteration AS INT,
               @i AS INT,
               @checkSum AS INT
              
       SET @checkSumTotal = 105
       SET @iteration = 1
       SET @i = 1
      
       WHILE @i < @stringLength
       BEGIN
           DECLARE @currentPair AS INT,
                   @resultPair AS INT,
                   @strCurrentPair AS VARCHAR(2)
          
           -- Get the pairs of numbers
           SET @strCurrentPair = SUBSTRING(@stringToEncode, @i, 2)
           SET @currentPair = CAST(@strCurrentPair AS INT)
           SET @resultPair = CASE WHEN @strCurrentPair = '00' THEN 194 WHEN @currentPair < 95 THEN 32 ELSE 100 END
           SET @encodedString = @encodedString + CHAR(@currentPair + @resultPair)
          
           SET @currentPair = @currentPair * @iteration
           SET @checkSumTotal = @checkSumTotal + @currentPair
           SET @iteration = @iteration + 1
              
           SET @i = @i + 2 -- Increment the step with 2
       END
      
       SET @checkSum = @checkSumTotal % 103
      
       RETURN @encodedString + CHAR(@checkSum + (CASE WHEN @checkSum < 95 THEN 32 ELSE 100 END)) + CHAR(206)
   END
  
   RETURN ''
END

You can use it like this:

SELECT dbo.CodeTo128C('1234567890')

Want more? Subscribe and Leave a comment! :)