Simple Checksum 1

[Custom24]

Hi
I was wondering if anyone could point me to the explanation for a simple checksum algorithm I could write in PL/SQL. It doesn't have to be very fast, and it only has to detect typographical errors in 5,6 or 7 digit numbers.

I looked at CRC, but I could not figure out what that was doing, never mind how to write it in PL/SQL. I need to understand the algotithm because I need to write it in VB.net and Java as well as PL/SQL

Thanks for any help.

Mark

 

[LKBrwnDBA]

Old banking CRC function:

Create Or Replace Function Chks(N0 Varchar2)
Return Varchar2 Is
I Pls_Integer;
J Pls_Integer;
K Pls_Integer:=0;
Begin
  For I In 1..Length(N0)
  Loop
    K:=K+(To_Number(Substr(N0,I,1))*I);
  End Loop;
  J:=Mod(Mod(K,11),10);
  Dbms_Output.Put_Line('K='||K||', J='||J);
  Return N0||'-'||To_Char(J,'Fm0');
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Custom24]

Thank you.
Testing this shows that out of about 4,500 errors where any 2 digits are swapped in codes of 4 or five digits, it fails to detect about 2% of the time.

Out of about the same number where 2 consecutive digits are swapped (the most common kind of error), it fails also about 2% of the time.

This is good enough for me. I have included the test code below. It is very rough and ready

CREATE TABLE TEST_CHECKSUM
(
  CORRECT_INPUT       VARCHAR2(15),
  CHECKSUM_ERR_INPUT  VARCHAR2(1),
  CHECKSUM_CORRECT    VARCHAR2(1),
  ERR_INPUT           VARCHAR2(15),
  TYPE                VARCHAR2(10)
);

CREATE OR REPLACE PROCEDURE PopTestChks AS
   i PLS_INTEGER;
   corr_in PLS_INTEGER;
   err_in PLS_INTEGER;
   first_swap PLS_INTEGER;
   second_swap PLS_INTEGER;
   temp VARCHAR2(12);
BEGIN
   DELETE FROM test_checksum;

   FOR i IN 1 .. 5000 LOOP
      corr_in := ROUND(DBMS_RANDOM.VALUE(1000, 1000000), 0);

--swap two random digits - make sure that the value for second is greater than first
--there is an easy opt here but I could not be bothered
      <<try_again>>
      first_swap := ROUND(DBMS_RANDOM.VALUE(1, LENGTH(corr_in)));
      second_swap := ROUND(DBMS_RANDOM.VALUE(1, LENGTH(corr_in)));

      IF second_swap <= first_swap THEN
         GOTO try_again;
      END IF;

      temp :=
         SUBSTR(corr_in, 1, first_swap - 1) || SUBSTR(corr_in, second_swap, 1)
         || SUBSTR(corr_in, first_swap + 1, second_swap - first_swap - 1) || SUBSTR(corr_in, first_swap, 1)
         || SUBSTR(corr_in, second_swap + 1, LENGTH(corr_in) - second_swap);
      err_in := TO_NUMBER(temp);

      INSERT INTO test_checksum t
                  (t.CORRECT_INPUT, t.ERR_INPUT, t.TYPE)
           VALUES (corr_in, err_in, 'Any 2');
   END LOOP;

   --now do it again, but this time, just swap two consecutive digits
   FOR i IN 1 .. 5000 LOOP
      corr_in := ROUND(DBMS_RANDOM.VALUE(1000, 1000000), 0);
      first_swap := ROUND(DBMS_RANDOM.VALUE(1, LENGTH(corr_in) - 1));
      second_swap := first_swap + 1;
      temp :=
         SUBSTR(corr_in, 1, first_swap - 1) || SUBSTR(corr_in, second_swap, 1)
         || SUBSTR(corr_in, first_swap + 1, second_swap - first_swap - 1) || SUBSTR(corr_in, first_swap, 1)
         || SUBSTR(corr_in, second_swap + 1, LENGTH(corr_in) - second_swap);
      err_in := TO_NUMBER(temp);

      INSERT INTO test_checksum t
                  (t.CORRECT_INPUT, t.ERR_INPUT, t.TYPE)
           VALUES (corr_in, err_in, 'Consec');
   END LOOP;

   UPDATE test_checksum t
      SET t.CHECKSUM_CORRECT = chks(t.CORRECT_INPUT),
          t.CHECKSUM_ERR_INPUT = chks(t.ERR_INPUT);

   COMMIT;
END;

select count(1) from test_checksum t where t.CORRECT_INPUT != t.ERR_INPUT and t.CHECKSUM_CORRECT = t.CHECKSUM_ERR_INPUT and t.TYPE = 'Any 2'; --106

select count(1) from test_checksum t where t.CORRECT_INPUT = t.ERR_INPUT and t.TYPE = 'Any 2'; --493

select count(1) from test_checksum t where t.CORRECT_INPUT != t.ERR_INPUT and t.CHECKSUM_CORRECT = t.CHECKSUM_ERR_INPUT and t.TYPE = 'Consec'; --84

select count(1) from test_checksum t where t.CORRECT_INPUT = t.ERR_INPUT and t.TYPE = 'Consec';  --521

Mark

 

[Custom24]

Oh forgot to mention that to test the code I changed the function so that it just returns the checksum digit, rather than the concatenation of the input and checksum.



Mark

 

[Custom24]

I just had a thought. I was wondering why you mod 10 the result of the mod 11 operation. And then I figured that you did this because your check digit had to be a single numeric character.

Mine doesn't. If I just leave it as mod 11, and use 'A' or something when the result of the mod 11 operation is 10, then the fail rate falls by a factor of 10. Most of the 2% fails were simply because of this.

I was planning to use a letter rather than a digit anyway.
Excellent!

Thanks again.

Mark

 

[LKBrwnDBA]

OK, try this version:

Create Or Replace Function Chks(N0 Varchar2)
Return Varchar2 Is
I Pls_Integer;
J Pls_Integer;
K Pls_Integer:=0;
Begin
  For I In 1..Length(N0)
  Loop
    K:=K+MOD(To_Number(Substr(N0,I,1))*I,11);
  End Loop;
  J:=Mod(K,10);
  Return To_Char(J,'Fm0');
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Custom24]

That comes out at about 5%, which is worse.

I think the reason is still the mod 10 step at the end. The clever bit is mod 11 - I think it is undoing some of the cleverness to do a mod 10 as well.

I am not an expert on checksums obviously , so I am not sure.

Thanks

Mark