USPS ACS Keyline Check Digit

I have implemented the "MOD 10" check digit algorithm using SQL, for the US Postal Service Address Change Service Keyline according to the method in their document, but it seems I'm getting the wrong numbers! Our input strings have only numbers in them, making the calculation a little easier. When I compare my results with the results from their testing application, I get different numbers. I don't understand what is going on? Does anyone see anything wrong with my algorithm? It's got to be something obvious...

The documentation for the method can be found on page 12-13 of this document: http://www.usps.com/cpim/ftp/pubs/pub8a.pdf

The sample application can be found at: http://ribbs.usps.gov/acs/documents/tech_guides/KEYLINE.EXE

PLEASE NOTE: I fixed the code below, based on the help from forum users. This is so that future readers will be able to use the code in its entirety.

ALTER function [dbo].[udf_create_acs] (@MasterCustomerId varchar(26))
returns varchar(30)
as
begin
 --this implements the "mod 10" check digit calculation
 --for the US Postal Service ACS function, from "Publication 8A"
 --found at "http://www.usps.com/cpim/ftp/pubs/pub8a.pdf"
 declare @result varchar(30)
 declare @current_char int
 declare @char_positions_odd varchar(10)
 declare @char_positions_even varchar(10)
 declare @total_value int
 declare @check_digit varchar(1)

 --These strings represent the pre-calculated values of each character
 --Example: '7' in an odd position in the input becomes 14, which is 1+4=5
 -- so the '7' is in position 5 in the string - zero-indexed
 set @char_positions_odd = '0516273849'
 set @char_positions_even = '0123456789'
 set @total_value = 0
 set @current_char = 1

 --stepping through the string one character at a time
 while (@current_char <= len(@MasterCustomerId)) begin
  --this is the calculation for the character's weighted value
  if (@current_char % 2 = 0) begin
   --it is an even position, so just add the digit's value
   set @total_value = @total_value + convert(int, substring(@MasterCustomerId, @current_char, 1))
  end else begin
   --it is an odd position, so add the pre-calculated value for the digit
   set @total_value = @total_value + (charindex(substring(@MasterCustomerId, @current_char, 1), @char_positions_odd) - 1)
  end

  set @current_char = @current_char + 1
 end

 --find the check digit (character) using the formula in the USPS document
 set @check_digit = convert(varchar,(10 - (@total_value % 10)) % 10)

 set @result = '#' + @MasterCustomerId + '   ' + @check_digit + '#'

 return @result
end

From stackoverflow

• Why do we have an additional mod:

  convert(varchar, 10 % <<-- ?

  The document says that only the last digit needs to be subtracted from 10. Did I miss anything?

  Jasmine : The extra mod 10 is so I end up with only the right-most digit.

• set @check_digit = convert(varchar, (10 - (@total_value % 10)) % 10)
  

  Jasmine : DUH! I knew I had something backwards :) Thanks!

• I'm not sure why you're messing with the whole string representations when you're working in a set-based language.

  I'd probably do it like below. I ran four tests through and they were all successful. You can expand this easily to handle characters as well and you could even make the table permanent if you really wanted to do that.

  CREATE FUNCTION dbo.Get_Mod10
  (
      @original_string VARCHAR(26)
  )
  RETURNS VARCHAR(30)
  AS
  BEGIN
      DECLARE
       @value_mapping TABLE (original_char CHAR(1) NOT NULL, odd_value TINYINT NOT NULL, even_value TINYINT NOT NULL)
  
      INSERT INTO @value_mapping
      (
       original_char,
       odd_value,
       even_value
      )
      SELECT '0', 0, 0 UNION
      SELECT '1', 2, 1 UNION
      SELECT '2', 4, 2 UNION
      SELECT '3', 6, 3 UNION
      SELECT '4', 8, 4 UNION
      SELECT '5', 1, 5 UNION
      SELECT '6', 3, 6 UNION
      SELECT '7', 5, 7 UNION
      SELECT '8', 7, 8 UNION
      SELECT '9', 9, 9
  
      DECLARE
       @i    INT,
       @clean_string VARCHAR(26),
       @len_string  TINYINT,
       @sum   SMALLINT
  
      SET @clean_string = REPLACE(@original_string, ' ', '')
      SET @len_string = LEN(@clean_string)
      SET @i = 1
      SET @sum = 0
  
      WHILE (@i <= @len_string)
      BEGIN
       SELECT
        @sum = @sum + CASE WHEN @i % 2 = 0 THEN even_value ELSE odd_value END
       FROM
        @value_mapping
       WHERE
        original_char = SUBSTRING(@clean_string, @i, 1)
  
       SET @i = @i + 1
      END
  
      RETURN (10 - (@sum % 10)) % 10
  END
  GO
  

  Jasmine : Well, I did an explicit implementation because I want it to be understandable to programmers in the future, and nobody in this shop speaks SQL. Also, when I did a similar thing as you have, it didn't work. Will try your idea later today and let you know if it works. Thanks!