Wednesday, 31 May 2006

Calculating a Code 39 Checksum digit in Sql

In case anyone has to create a barcode using code 39 to encode it – here is a bit of sql code I created to generate the checksum digit:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



alter FUNCTION dbo.code39CheckDigit
( @Data varchar(255) )
RETURNS varchar(256)
AS
BEGIN
declare @Res int
declare @Counter int
declare @CurrentChar nvarchar(1)
declare @CheckDigitInt int
declare @CheckDigitVar varchar(1)
set @Counter = 0
set @Res = 0
while(@Counter < counter =" @Counter" currentchar =" lower(substring(@Data," res =" @Res" res =" @Res" checkdigitint =" @Res" checkdigitvar =" case"> 9 then nchar(@CheckDigitInt + 87)
when @CheckDigitInt= 36 then '-'
when @CheckDigitInt= 37 then '.'
when @CheckDigitInt= 38 then ' '
when @CheckDigitInt= 39 then '$'
when @CheckDigitInt= 40 then '/'
when @CheckDigitInt= 41 then '+'
when @CheckDigitInt= 42 then '%'
END


return @Data + upper(@CheckDigitVar)
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This return the original data along with the checksum – here describes the algorithm http://www.barcodeman.com/info/c39_1.php3

No comments: