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



ALTER FUNCTION [dbo].[CodeTo128B] (@stringToEncode VARCHAR(200))
RETURNS VARCHAR(200) AS
BEGIN

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

;with mycte as

(
Select 1 as i 
 
, @stringToEncode as stringToEncode 
,   SUBSTRING(@stringToEncode, 1, 1)   as currentPair 
,Cast(CAST(CHAR(204) as varchar(200)) +  SUBSTRING(@stringToEncode,  1, 1)    as varchar(200)  ) AS encodedString
, (ascii( SUBSTRING(@stringToEncode, 1, 1) )-32 ) as checkSumTotal 

Union all

Select m.i+1 as i  
,m.stringToEncode 
, SUBSTRING(m.stringToEncode, m.i+1, 1)  
,Cast(m.encodedString +  SUBSTRING(stringToEncode, m.i+1, 1)      as varchar(200)  )
,m.checkSumTotal +  (m.i +1) *(ascii(SUBSTRING(stringToEncode, m.i+1, 1) )-32)
 
FROM mycte m

WHERE m.i<=len(@stringToEncode)
)

,mycte1 as (
Select encodedString, checkSumTotal, ((104+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

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