Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Barcodes Code128 generator function Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 5:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
I'm looking for a barcode generating Function (in SQL 2005), that uses the standard Code128.

The result of calling the function will then be mailmerged in Word in a document with font Code128.TTF (this shows the barcode as such).

Now, the problem is that I have the correct function in Access (code attached in TXT) but not in SQL Server.

Does anyone have the equivalent SQL Server function (or knows how to translate this Access into SS) ?

Thanks in advance, a.


  Post Attachments 
basCode128.txt (68 views, 3.77 KB)
Post #1062489
Posted Friday, February 11, 2011 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
a quick google lead me to this solution posted from Dalton from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70901

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



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1062506
Posted Friday, February 11, 2011 7:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Thanks Lowell, but it doesn't seem to be the same standard I'm referring to.

In standard Code128, if you code 'bananas', you'll get ÑbananasÈÓ (it always starts with capital Ñ and ends with Ó).

Pass 'bananas' to this function and you'll get 'š' and a weird character in the end... they must be pretty similar standards, but not equal.
Post #1062574
Posted Friday, July 26, 2013 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 28, 2013 9:18 PM
Points: 2, Visits: 6
anyone know a way that can create these codes without cost?
Post #1477879
Posted Tuesday, July 30, 2013 6:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
Maybe try this?

http://www.dafont.com/code-128.font
Post #1478955
Posted Tuesday, July 30, 2013 6:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
take a look at teh script mister magoo built in this similar barcode 128b thread:
http://www.sqlservercentral.com/Forums/Topic1470917-392-1.aspx

with that and a bar code font, i think you would have everything you need


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478976
Posted Thursday, June 26, 2014 9:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 9:10 PM
Points: 2, Visits: 1
a_ud (2/11/2011)
I'm looking for a barcode generating Function (in SQL 2005), that uses the standard Code128.

The result of calling the function will then be mailmerged in Word in a document with font
Code128.TTF (this shows the barcode as such).

Now, the problem is that I have the correct function in Access (code attached in TXT) but not in SQL Server.

Does anyone have the equivalent SQL Server function (or knows how to translate this Access into SS) ?

Thanks in advance, a.



I have seen few threads on this issue, like http://www.sqlservercentral.com/Forums/Topic1065554-149-1.aspx, but does anyone have successfully solved this question with own experence?
Post #1586712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse