31 March 2011

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! :)

4 comments:

multithreadthoughts said...

Just saved me several hours of trial and error... Not that I mind the work effort, just have a customer breathing down my neck...

Thanks for this!

Anonymous said...

I had a problem whenever it tried to encode '00'.
I fixed it by changing this line:
SET @resultPair = CASE WHEN @strCurrentPair = '00' THEN 194
into:
set @resultPair = case when @strCurrentPair = '00' then 207

Not sure why 207 worked but 194 didn't. Maybe its my font:
http://www.jtbarton.com/Barcodes/Code128.aspx

Aida Fooks said...

Great in sequence! There is something wonderful about "Code 128 C - SQL Function". I am fearful by the excellence of information on this website.
I think, The presence of barcode is important if you would like to sell the products you have produced at your manufacture.
I am sure I will visit this place another time soon, to know about code 128.

Anonymous said...

what are the changes you need to generate for this no. 472211111111111100020

Post a Comment

your thoughts are welcome:

Need more? Leave comments and subscribe to my blog.

.