BARCODE 128 Encoding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

BARCODE 128 Encoding

Author

Topic

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-23 : 09:36:09

The following TSQL code will take a character string and perform the encoding that is necessary to generate a BARCODE 128 formatted string to be used with a BARCODE 128 font.[CODE]declare @myString varchar(255)
select @myString = 'BarCode 1'

-- Define the string of characters that we'll need to pull the reference of
declare @asciiString varchar(255)
select @asciiString = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~'
select @asciiString = @asciiString + char(195) -- 0xC3
select @asciiString = @asciiString + char(196) -- 0xC4
select @asciiString = @asciiString + char(197) -- 0xC5
select @asciiString = @asciiString + char(198) -- 0xC6
select @asciiString = @asciiString + char(199) -- 0xC7
select @asciiString = @asciiString + char(200) -- 0xC8
select @asciiString = @asciiString + char(201) -- 0xC9
select @asciiString = @asciiString + char(202) -- 0xCA
-- Define the stop and start characters
declare @stopchar char(1)
declare @startchar char(1)
declare @spacechar char(1)
select @stopchar = char(206) -- 0xCE
select @startchar = char(204) -- 0xCC
select @spacechar = char(194) -- 0xC2

-- Define the final holding place of our output string
declare @finalArray varchar(255)

-- Define the variables that we'll need to be using
declare @checksumTotal int
declare @checksum int
select @checksumTotal = 104;
select @checksum = 0;

-- Start building our output
select @finalArray = @startchar

-- Loop through our input variable and start pulling out stuff
declare @position int
declare @thisChar char(1)
select @position = 1
while @position <= len(@myString)
begin
select @thisChar = substring(@myString, @position, 1)
select @checksumTotal = @checksumTotal + (@position * (ascii(@thischar)-32))
select @finalArray = @finalArray + @thisChar
select @position = @position + 1
end -- We've gone past the length now

-- Now we need to figure out and add the checksum character
select @checksum = @checksumTotal % 103
if @checksum = 0
select @finalArray = @finalArray + @spacechar
else
-- Barcorde array assumes 0 as initial offset so we need to add 1 to checksum
select @finalArray = @finalArray + substring(@asciiString, @checksum+1, 1)
-- Now we append the stop character
select @finalArray = @finalArray + @stopchar

-- The @final Array represents the barcode encoded string
select @finalArray[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Barcode
Starting Member

11 Posts

Posted - 2012-11-20 : 22:32:03

Thanks, this helps me a lot with my barcode project on report solution :P
unspammed

ChickenCanoe
Starting Member

1 Post

Posted - 2012-11-30 : 15:50:12

I believe this method contains an error. The ascii string should be:

select @asciiString = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'
select @asciiString = @asciiString + char(200) -- 0xC8
select @asciiString = @asciiString + char(201) -- 0xC9
select @asciiString = @asciiString + char(202) -- 0xCA
select @asciiString = @asciiString + char(203) -- 0xCB
select @asciiString = @asciiString + char(204) -- 0xCC
select @asciiString = @asciiString + char(205) -- 0xCD
select @asciiString = @asciiString + char(206) -- 0xCE
select @asciiString = @asciiString + char(207) -- 0xCF

As per: http://en.wikipedia.org/wiki/Code_128#Check_digit_calculation

ValentineVic
Starting Member

1 Post

Posted - 2013-05-31 : 05:43:52

The same class SQL Server barcode font encoder that is used to format data for barcode font.
http://www.keepautomation.com/font_tools/sql_reporting_services_barcode_font_encoder.html

recthor
Starting Member

2 Posts

Posted - 2013-07-16 : 02:34:24

thanks for sharing, that seems great. i am using some different tools to generate barcode 128 which might serve as your reference. any difference between the two, which one is better?
http://www.rasteredge.com/how-to/csharp-imaging/barcode-generating-code128/
http://www.rasteredge.com/how-to/csharp-imaging/barcode-generating/

matthosking
Starting Member

1 Post

Posted - 2013-07-22 : 01:52:45

The function is correct for the IDAutomation fonts which use codes that are a little different from the standard Code 128. I've updated the function to allow for a smaller barcode using Code C (numeric only symbols) when the rest of the barcode is numbers:

CREATE FUNCTION dbo.CreateBarcodeCode128
(
@Barcode VARCHAR(255)
)
RETURNS VARCHAR(255)
BEGIN

-- Define the final holding place of our output string
DECLARE @finalArray VARCHAR(255)

IF (@Barcode IS NOT NULL)
BEGIN
-- Define the string of characters that we'll need to pull the reference of
DECLARE @asciiString VARCHAR(255)
SELECT @asciiString = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'
SELECT @asciiString = @asciiString + CHAR(195) -- 0xC3
SELECT @asciiString = @asciiString + CHAR(196) -- 0xC4
SELECT @asciiString = @asciiString + CHAR(197) -- 0xC5
SELECT @asciiString = @asciiString + CHAR(198) -- 0xC6
SELECT @asciiString = @asciiString + CHAR(199) -- 0xC7
SELECT @asciiString = @asciiString + CHAR(200) -- 0xC8
SELECT @asciiString = @asciiString + CHAR(201) -- 0xC9
SELECT @asciiString = @asciiString + CHAR(202) -- 0xCA
-- Define the stop and start characters
DECLARE @stopchar CHAR(1)
DECLARE @startchar CHAR(1)
DECLARE @spacechar CHAR(1)
SELECT @stopchar = CHAR(206) -- 0xCE
SELECT @startchar = CHAR(204) -- 0xCC
SELECT @spacechar = CHAR(194) -- 0xC2

-- Define the variables that we'll need to be using
DECLARE @checksumTotal INT
DECLARE @checksum INT
SELECT @checksumTotal = 104;
SELECT @checksum = 0;

-- Start building our output
SELECT @finalArray = @startchar

-- Loop through our input variable and start pulling out stuff
DECLARE @position INT
DECLARE @outputIndex INT
DECLARE @thisChar CHAR(1)
DECLARE @thisInt INT
SELECT @position = 1
SELECT @outputIndex = 1

-- Process data in Code B until the result is of event length and entirely numeric
WHILE @position <= LEN(@Barcode)
AND (@position % 2 = 0
OR ISNUMERIC(SUBSTRING(@Barcode, @position, 100)) != 1)
BEGIN
SELECT @thisChar = SUBSTRING(@Barcode, @position, 1)
SELECT @checksumTotal = @checksumTotal + (@outputIndex * (ASCII(@thischar) - 32))
SELECT @finalArray = @finalArray + @thisChar
SELECT @position = @position + 1
SELECT @outputIndex = @outputIndex + 1
END -- We've gone past the length now

-- Switch to Code C for numeric
IF (@position <= LEN(@Barcode))
BEGIN
SELECT @checksumTotal = @checksumTotal + (@outputIndex * 99)
SELECT @finalArray = @finalArray + CHAR(199)
SELECT @outputIndex = @outputIndex + 1
END

-- Look up remaining numbers in pairs using Code C
WHILE @position <= LEN(@Barcode)
BEGIN
SELECT @thisInt = CONVERT(INT, SUBSTRING(@Barcode, @position, 2))
SELECT @checksumTotal = @checksumTotal + (@outputIndex * @thisInt)
IF (@thisInt = 0)
SELECT @finalArray = @finalArray + CHAR(194)
ELSE
SELECT @finalArray = @finalArray + SUBSTRING(@asciiString, @thisInt + 1, 1)
SELECT @position = @position + 2
SELECT @outputIndex = @outputIndex + 1
END

-- Now we need to figure out and add the checksum character
SELECT @checksum = @checksumTotal % 103
IF @checksum = 0
SELECT @finalArray = @finalArray + @spacechar
ELSE
-- Barcorde array assumes 0 as initial offset so we need to add 1 to checksum
SELECT @finalArray = @finalArray + SUBSTRING(@asciiString, @checksum + 1, 1)
-- Now we append the stop character
SELECT @finalArray = @finalArray + @stopchar
END

-- The @final Array represents the barcode encoded string
RETURN @finalArray
END

festhest
Starting Member

1 Post

Posted - 2013-07-29 : 22:13:29

I used this SQL Server Reporting Service Barcode Generator(http://www.onbarcode.com/products/net_barcode_reporting_service/barcodes/code_128.html)to create Code 128 barcode in SQL Server Reporting Service. Here is the methods:
// generate barcode and encode to image file
public void drawBarcode(string filename)

// generate barcode and paint on Graphics object
public void drawBarcode(Graphics graphics)

// generate barcode and paint on Bitmap object
public Bitmap drawBarcode()

// generate barcode and paint on Stream object
public void drawBarcode(Stream fileStream)

// generate barcode and paint on byte[] object
public byte[] drawBarcodeAsBytes()
For more info, you can read this:
http://www.onbarcode.com/tutorial/net-barcode-generation-reporting-service-2008.html

cindy313
Starting Member

7 Posts

Posted - 2013-08-01 : 04:41:10

as for code 128 barcode generator,I'd like to share this for you guys,hope it works for you http://www.keepautomation.com/online_barcode_generator/code_128/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources