T-SQL UDF Function: Generate Barcode — Code 128C


I have a need to generate barcode from an indeity column of a table. I write a T-SQL function to use code128c spec.


CREATE FUNCTION [dbo].[CodeTo128C] (@stringToEncode VARCHAR(200))
RETURNS VARCHAR(200) AS
BEGIN

   DECLARE @encodedString AS VARCHAR(200) =''
   
  
  IF LEN(@stringToEncode) > 0
   
   BEGIN

;with mycte as

(
Select 0 as i 
, @stringToEncode as stringToEncode 
, Cast( SUBSTRING(@stringToEncode, 0, 2) as int) as currentPair 
,Cast(CAST(CHAR(205) as varchar(200)) + CHAR( Cast( SUBSTRING(@stringToEncode, 0, 2) as int)+ CASE WHEN Cast( SUBSTRING(@stringToEncode, 0, 2) as int) < 95 THEN 32 ELSE 100 END )  as varchar(200) ) AS encodedString
,105 +  Cast( SUBSTRING(@stringToEncode, 0, 2) as int) as checkSumTotal 

Union all

Select m.i+2 as i 
,m.stringToEncode 
, Cast(SUBSTRING(m.stringToEncode, m.i+2, 2) as int) 
,Cast(m.encodedString + CHAR( Cast(SUBSTRING(m.stringToEncode, m.i+2, 2) as int)+ CASE WHEN  Cast(SUBSTRING(m.stringToEncode, m.i+2, 2) as int) < 95 THEN 32 ELSE 100 END )  as varchar(200) ) 
,m.checkSumTotal +  Cast(SUBSTRING(stringToEncode, m.i+2, 2) as int) *( (m.i+2)/2+1)    

FROM mycte m
WHERE m.i<len(@stringToEncode)
)

,mycte1 as (
Select encodedString, checkSumTotal, (checkSumTotal % 103) as mycheckSum , row_number() Over(Order by i DESC) rn from mycte) 
Select @encodedString= Cast(encodedString + CHAR(mycheckSum + (CASE WHEN mycheckSum < 95 THEN 32 ELSE 100 END)) + CHAR(206) as varchar(200))    from mycte1
WHERE rn=2


 RETURN @encodedString
END
   
   RETURN ''
END




 

The font download:
http://www.dafont.com/code-128.font
Reference:
http://extremedev.blogspot.com/2011/03/code-128-c-sql-function.html

http://stackoverflow.com/questions/13909248/generating-code-128-barcodes-using-excel-vba

http://www.sqlservercentral.com/Forums/Topic1470917-392-1.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s