7 Replies Latest reply on Jul 22, 2007 11:09 AM by 547235

    EAN-13 checksum

    547235
      I need to validate the input of EAN-13 check digit
      I use FORMS 4.5 & ORCALE DB 9i

      2741023456884 (this is the scanned input)

      2*1
      7*3
      4*1
      1*3
      0*1
      2*3
      3*1
      4*3
      5*1
      6*3
      8*1
      8*3

      2 + 21 + 4+3+0+6+3+12+5+18+8+24=106

      106/10 = 10 remainder 6

      10-6 =4 ( 4 matches the check digit in the above input last digit)

      Note: if the remainder is 0 then 10 -0 = 10 and the check digit is the right most digit which is 0

      Can any help me with this procedure, has any one did this before.

      Thanks for you help in advance
        • 1. Re: EAN-13 checksum
          561093
          create or replace function ean_13(P_Str In Varchar2) Return Varchar2 Is
          l_Sum Number;
          l_Multiple Number;
          Begin
          For I in 1..12 Loop
          If mod(i,2) = 0 Then
          l_Multiple := 3;
          Else
          l_Multiple := 1;
          End If;

          l_Sum := Nvl(l_Sum, 0) + Substr(P_Str, i, 1) * l_Multiple;
          End Loop;
          If Floor(l_Sum/10) - Mod(l_Sum, 10) = Substr(P_Str, 13) Then
          Return('TRUE');
          Else
          Return('FALSE');
          End If;
          End;
          /


          SQL> select ean_13('2741023456884') from dual;

          EAN_13('2741023456884')
          -------------------------------------------------------
          TRUE

          SQL> select ean_13('2741023456881') from dual;

          EAN_13('2741023456881')
          -------------------------------------------------------
          FALSE
          • 2. Re: EAN-13 checksum
            547235
            Thanks for you prompt response,

            why it does not work for the following:
            2741012605644
            2741012605651
            2741000000017
            it returns false.
            • 3. Re: EAN-13 checksum
              561093
              create or replace function ean_13(P_Str In Varchar2) Return Varchar2 Is
              l_Sum Number;
              l_Multiple Number;
              Begin
              For I in 1..12 Loop
              If mod(i,2) = 0 Then
              l_Multiple := 3;
              Else
              l_Multiple := 1;
              End If;

              l_Sum := Nvl(l_Sum, 0) + Substr(P_Str, i, 1) * l_Multiple;
              End Loop;

              If 10 - Mod(l_Sum, 10) = Substr(P_Str, 13) Then
              Return('TRUE');
              Else
              Return('FALSE');
              End If;
              End;
              /


              SQL> select ean_13('2741012605644') from dual;

              EAN_13('2741012605644')
              ------------------------------------------------
              TRUE


              SQL> select ean_13('2741012605651') from dual;

              EAN_13('2741012605651')
              --------------------------------------------------
              TRUE


              SQL> select ean_13('2741000000017') from dual;

              EAN_13('2741000000017')
              ------------------------------------------------
              TRUE
              • 4. Re: EAN-13 checksum
                547235
                THANKS BUDDY...YOU SAVED MY DAY
                • 5. Re: EAN-13 checksum
                  547235
                  HI CITRUS,

                  DO YOU HAVE THE OUTPUT PROCEDURE FOR GENERATING TEXT FILE FOR THE AIRMILES PROGRAMME

                  THANKS.
                  • 6. Re: EAN-13 checksum
                    572471
                    SQL> with t as (select '2741023456884' str from dual)
                      2  --
                      3  select decode(substr(10 - mod(sum(substr(str, level, 1) *
                      4                             decode(mod(level, 2), 1, 1, 3)),
                      5                         10),-1,1),
                      6                substr(str, -1, 1),
                      7                'TRUE',
                      8                'FALSE')
                      9    from t
                     10  connect by level < length(str)
                     11  /
                    
                    DECODE(SUBSTR(10-MOD(SUM(SUBST
                    ------------------------------
                    TRUE
                    
                    SQL> 
                    • 7. Re: EAN-13 checksum
                      547235
                      thanks volder