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 (71 views, 3.77 KB)
Post #1062489
Posted Friday, February 11, 2011 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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: Friday, December 12, 2014 6:22 AM
Points: 531, Visits: 449
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: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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
Posted Thursday, December 4, 2014 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 9:35 AM
Points: 1, Visits: 0
Hi all,

I hope someone can help. I´m trying to resolve this but no luck. ALl the functions I got from the internet don´t make the CODE128 as requested by the client.

the only website that does for code 128:
http://www.barcode-generator.org/

Example:
for the code entered:1304083202
He gets back using the VB code below:Ò-$(@"rÓ
with the TSQL function I´ve got I get back: Í1304083202sÎ

Completly different. If some1 can help It would be great.

This is the code he wants to translate to T-SQL
Public Function code128$(chaine$)
'This function is governed by the GNU Lesser General Public License (GNU LGPL)
'V 2.0.0
'Parameters : a string
'Return : * a string which give the bar code when it is dispayed with CODE128.TTF font
' * an empty string if the supplied parameter is no good
Dim i%, checksum&, mini%, dummy%, tableB As Boolean
code128$ = ""

If Len(chaine$) > 0 Then
'Check for valid characters
For i% = 1 To Len(chaine$)
Select Case Asc(Mid$(chaine$, i%, 1))
Case 32 To 126, 203
Case Else
i% = 0
Exit For
End Select
Next
'Calculation of the code string with optimized use of tables B and C
code128$ = ""
tableB = True
If i% > 0 Then
i% = 1 'i% become the string index
Do While i% <= Len(chaine$)
If tableB Then
'See if interesting to switch to table C
'yes for 4 digits at start or end, else if 6 digits
mini% = IIf(i% = 1 Or i% + 3 = Len(chaine$), 4, 6)
GoSub testnum
If mini% < 0 Then 'Choice of table C
If i% = 1 Then 'Starting with table C
code128$ = Chr$(210)
Else 'Switch to table C
code128$ = code128$ & Chr$(204)
End If
tableB = False
Else
If i% = 1 Then code128$ = Chr$(209) 'Starting with table B
End If
End If
If Not tableB Then
'We are on table C, try to process 2 digits
mini% = 2
GoSub testnum
If mini% < 0 Then 'OK for 2 digits, process it
dummy% = Val(Mid$(chaine$, i%, 2))
dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 105)
code128$ = code128$ & Chr$(dummy%)
i% = i% + 2
Else 'We haven't 2 digits, switch to table B
code128$ = code128$ & Chr$(205)
tableB = True
End If
End If
If tableB Then
'Process 1 digit with table B
code128$ = code128$ & Mid$(chaine$, i%, 1)
i% = i% + 1
End If
Loop
'Calculation of the checksum
For i% = 1 To Len(code128$)
dummy% = Asc(Mid$(code128$, i%, 1))
dummy% = IIf(dummy% < 127, dummy% - 32, dummy% - 105)
If i% = 1 Then checksum& = dummy%
checksum& = (checksum& + (i% - 1) * dummy%) Mod 103
Next
'Calculation of the checksum ASCII code
checksum& = IIf(checksum& < 95, checksum& + 32, checksum& + 105)
'Add the checksum and the STOP
code128$ = code128$ & Chr$(checksum&) & Chr$(211)
End If
End If
Exit Function
testnum:
'if the mini% characters from i% are numeric, then mini%=0
mini% = mini% - 1
If i% + mini% <= Len(chaine$) Then
Do While mini% >= 0
If Asc(Mid$(chaine$, i% + mini%, 1)) < 48 Or Asc(Mid$(chaine$, i% + mini%, 1)) > 57 Then Exit Do
mini% = mini% - 1
Loop
End If
Return
End Function


Post #1641562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse