Free BarCode128 SQL编码

CREATE   FUNCTION StringToBarcode
 (@Value Varchar(50)   )
RETURNS NVarchar(100)
AS
BEGIN
 Declare @charCount int
 Declare  @charPos int, @minCharPos int
 declare @currentChar int, @checksum int
   Declare @isTableB int,@isValid int
   Declare @returnValue NVarchar(100)
 
 set @isTableB=1
 set @isValid=1
 set @returnValue=''

 if Len(@Value)>0
 BEGIN
  set @charCount=1
  while @charCount<=Len(@Value)
  BEGIN
   SET @currentChar = ASCII(Substring(@value,@charCount, 1))
   IF NOT (@currentChar>=32 AND @currentChar<=126)
   BEGIN
    SET @isValid=0
    break
   END
   SET @charCount=@charCount+1
  END

  if @isValid=1
  BEGIN
   SET @charPos=1
   WHILE @charPos<=LEN(@value)
   BEGIN
    if @isTableB=1
    BEGIN
     --See if interesting to switch to table C
               --yes for 4 digits at start or end, else if 6 digits
     IF @charPos=1 or @charPos+3=Len(@Value)
      SET @minCharPos=4
     ELSE
      SET @minCharPos=6

     SET @minCharPos=dbo.IsNumber(@Value,@charPos, @minCharPos)
     if @minCharPos<0
     BEGIN
      --Choice table C
      IF @charPos=1
       --Starting with table C
       SET @returnValue=Nchar(205)
      ELSE
       --Switch to table C
                     SET @returnValue = @returnValue + Nchar(199)
      SET @isTableB=0
     END
     ELSE
     BEGIN
      if (@charPos = 1)                              
                  --Starting with table B
                   SET @returnValue = Nchar(204)
     END
    END

    ----------------------------
    if @isTableB=0
    BEGIN
     --// We are on table C, try to process 2 digits
     SET @minCharPos = 2
     SET @minCharPos=dbo.IsNumber(@Value,@charPos, @minCharPos)
     IF @minCharPos <0 -- OK for 2 digits, process it
     BEGIN
      SET @currentChar=(Substring(@Value,@charPos,2))
      if @currentChar<95
       SET @currentChar=@currentChar+32
      ELSE
       SET @currentChar=@currentChar+100

      SET @returnValue = @returnValue+NCHAR(@currentChar)
      SET @charPos=@charPos+2
     END
     ELSE
     BEGIN
      --// We haven't 2 digits, switch to table B
                  SET @returnValue = @returnValue + Nchar(200)
                  SET @isTableB = 1
     END
    END
           
    ----------------------------        
    IF @isTableB=1
    BEGIN     
     --Process 1 digit with table B
     SET @returnValue = @returnValue + Substring(@Value,@charPos, 1)
     set @charPos=@charPos+1
            END
   END
   
   --Calculation of the checksum
   declare @loop int
   set @loop=1
         set @checksum = 0
   
   While @loop<=Len(@returnValue)
         BEGIN
    set @currentChar = ASCII(Substring(@returnValue,@loop, 1))
    IF @currentChar<127
     SET @currentChar=@currentChar-32
    ELSE
     SET @currentChar=@currentChar-100
    IF @Loop=1
     set @checksum = @currentChar
    else
     set @checksum = (@checksum + (@loop-1) * @currentChar) % 103
    
    SET @loop=@loop+1
   END                 
                  
         --Calculation of the checksum ASCII code
         if  @checksum < 95
    set  @checksum =@checksum+ 32
   else
    set @checksum=@checksum + 100
         -- Add the checksum and the STOP
         set @returnValue = @returnValue +Nchar(@checksum)+Nchar(206)
  END
 END

 RETURN @returnValue
END

 

CREATE  FUNCTION IsNumber(
 @InputValue Varchar(50),
 @CharPos int,
 @MinCharPos int)
RETURNS int
AS
BEGIN
 Declare @ValLength int
 set @ValLength=Len(@InputValue)
 set @MinCharPos=@MinCharPos-1
   if (@CharPos + @MinCharPos <= @ValLength)
   BEGIN
  while (@MinCharPos >= 0)
  BEGIN
     if ASCII(Substring(@InputValue,@CharPos + @MinCharPos, 1)) < 48
       OR ASCII(Substring(@InputValue,@CharPos + @MinCharPos, 1)) > 57    
        break    
     set @MinCharPos=@MinCharPos-1
  END
 END
 return @MinCharPos
END