Using SQL to add a start character and a rest to the Code128 code

In the use of code128 code font printing bar code is printed out of the barcode, the scanner can not recognize the situation, this situation is because the text is set directly to the code128 font without assigning them a starting character and a rest caused.

After checking the data to find many people encounter such problems, especially the use of Word,excel directly set code128 font printing, the general curve of this problem.

I found a cow on the Internet. In Excel, a function that adds a start and rest to text is written in VBA. Really good

The code is as follows:

FunctionCODE128B (Tar asRange)'128B Code: ChrW (204).DimS $, i%, ss$, j%, curr%, Checkb%curr=tar.rows=tar.valuecheckb=1  'the code value for the start bit is 104 mod 103 =1 fori =1  to Len(s) SS=Mid(S, I,1) J=ASC(ss)'do not filter invalid characters, such as Chinese characters.    IfJ <135  ThenJ= J- +    ElseIfJ >134  ThenJ= J- -    End Ifcheckb= (checkb + i * j)Mod 103   'Calculate check digitNextIfCHECKB < the  andcheckb >0  Then  'some of the data directly for 103 of the mold, the explanation is not sufficient, because some check bit more than 127, the system will "eat" off them (with the rest).CHECKB = checkb + +ElseIfcheckb >94  Then 'font settings, the matrix is defined with 2 values. You can see the font file when you look at it.CHECKB = checkb + -End Ifcode128b=ChrW(204) & S &IIf(CHECKB,ChrW(CHECKB),Chr( +)) &ChrW(206)End FUNCTION

According to this code, it occurred to me that it was possible to migrate directly to SQL Server, and after printing, the scanning experiment was really possible, and the code was as follows:

Create FUNCTIONstrtocode128b (@Str NVARCHAR( $))--128B Code: ChrW (204) RETURNS NVARCHAR( $)  asBEGIN DECLARE @checkB INT  DECLARE @i INT,@j INT DECLARE @str2 NVARCHAR(2) SET @i=1  SET @checkB = 1  --the code value for the start bit is 104 mod 103 =1                  --  while @i <= LEN(@Str)  BEGIN  SET @str2 = SUBSTRING(@Str,@i,1)  SET @j = ASCII(@str2)--do not filter invalid characters, such as Kanji  IF @j<135   BEGIN   SET @j=@j- +  END  ELSE IF @j>134   BEGIN   SET @j=@j- -  END  SET @checkB =(@checkB + @i * @j)% 103   --Calculate check digit  SET @i=@i+1 END    IF @checkB< the  and @checkB>0 --some of the data directly for 103 of the mold, the explanation is not sufficient, because some check bit more than 127, the system will "eat" off them (with the rest). BEGIN  SET @checkB = @checkB +  + END ELSE IF @checkB > 94  --' font settings, the matrix is defined by 2 values. You can see the font file when you look at it. BEGIN  SET @checkB = @checkB +  - END      RETURN NCHAR(204)+ @Str +  Case  when @checkB>0  Then NCHAR(@checkB)ELSE NCHAR( +)END + NCHAR(206) END

The above problems are mainly encountered in the process of conversion:

The ASC () function in 1.VBA corresponds to the SQL function is ASCII ()

The SQL function corresponding to the ChrW () function in 2.VBA is NCHAR (). This requires special attention, because both ChrW and Hchar are Unicode characters. And if it is not possible to use char, just started not to notice, with the char function, after stitching, the returned string is always empty (or invisible character), the results of debugging for a long time do not know where the problem.

Using SQL to add a start character and a rest to the Code128 code