Need Luhn Modulus 16 coding example to generate a check digit in PL/SQL

Question

Does anyone have a working function available to use within Oracle using PL/SQL which implements the Luhn Mod 16 Algorithm to generate a check digit for an input code number such as the following example? 0B012722900021AC35B2

LOGIC

1. Map from HEX into Decimal equivalent 0 B 0 1 2 7 2 2 9 0 0 0 2 1 A C 3 5 B 2 - becomes 0 11 0 1 2 7 2 2 9 0 0 0 2 1 10 12 3 5 11 2

2. Start with the last character in the string and move left doubling every other number - Becomes 0 22 0 2 2 14 2 4 9 0 0 0 2 2 10 24 3 10 11 4

3. Convert the "double" to a Base 16 (Hexadecimal) format. If the conversion results in numeric output, retain the value. Becomes: 0 16 0 2 2 E 2 4 9 0 0 0 2 2 10 18 3 A 11 4

4. Reduce by splitting down any resultant values over a single digit in length. Becomes 0 (1+6) 0 2 2 E 2 4 9 0 0 0 2 2 10 (1+8) 3 A 11 4

5. Sum all digits. Apply the last numeric value returned from the previous sequence of calculations (if the current value is A-F substitute the numeric value from step 1) Becomes 0 7 0 2 2 7 2 4 9 0 0 0 2 2 10 9 3 5 11 4

6. The sum of al l digits is 79 (0+7+0+2+2+7+2+4+9+0+0+0+2+2+10+9+3+5+11+4)

7. Calculate the value needed to obtain the next multiple of 16, in this case the next multiple 16 is 80 therefore the value is 1

8. The associated check character is 1

Thanks Lee

Answer 1

How about this?

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);


DECLARE
    luhn VARCHAR2(100) := '0B012722900021AC35B2';

    digits VARCHAR_TABLE_TYPE;
    DigitSum INTEGER;
BEGIN

    SELECT REGEXP_SUBSTR(luhn, '.', 1, LEVEL)
    BULK COLLECT INTO digits
    FROM dual
    CONNECT BY REGEXP_SUBSTR(luhn, '.', 1, LEVEL) IS NOT NULL;  

    FOR i IN digits.FIRST..digits.LAST LOOP
        digits(i) := TO_NUMBER(digits(i), 'X'); -- Map from HEX into Decimal equivalent
        IF digits.COUNT MOD 2 = i MOD 2 THEN -- every second digit from left            
            digits(i) := 2 * TO_NUMBER(digits(i)); -- doubling number
            digits(i) := TO_CHAR(digits(i), 'fmXX'); -- Convert the "double" to a Base 16 (Hexadecimal) format
            IF (REGEXP_LIKE(digits(i), '^\d+$')) THEN
                -- Reduce by splitting down any resultant values over a single digit in length. 
                SELECT SUM(REGEXP_SUBSTR(digits(i), '\d', 1, LEVEL))
                INTO digits(i)
                FROM dual
                CONNECT BY REGEXP_SUBSTR(digits(i), '\d', 1, LEVEL) IS NOT NULL;
            END IF;    
        END IF;
    END LOOP;

    FOR i IN digits.FIRST..digits.LAST LOOP
        -- I don't understand step 5), let's simulate it
        IF digits(i) = 'E' THEN digits(i) := 7; END IF;
        IF digits(i) = 'A' THEN digits(i) := 5; END IF;
    END LOOP;

    -- The sum of all digits
    SELECT SUM(COLUMN_VALUE)
    INTO DigitSum
    FROM TABLE(digits);

    -- Calculate the value needed to obtain the next multiple of 16 
    DBMS_OUTPUT.PUT_LINE ( 16 - DigitSum MOD 16 );

END;

Answer 2

Here is a function which implements the steps you outlined in your question:

create or replace function get_luhn_16_check_digit 
    ( p_str in varchar)
    return pls_integer 
is
    b16 sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll() ;
    n16 sys.odcinumberlist := sys.odcinumberlist();
    tot simple_integer := 0;
    chk_digit pls_integer;
begin
    -- step 1)
    select to_number(tkn, 'X')
    bulk collect into n16
    from ( select substr(p_str, level, 1) as tkn
                 from dual
                 connect by level <= length(p_str));
    b16.extend(n16.count());

    for idx in 1..n16.count() loop
        if mod(idx, 2) = 0
        then 
            -- step 2) and 3)
            b16(idx) := to_char( to_char(n16(idx)) * 2, 'fmXX');
            -- step 4)
            if length( b16(idx)) = 2 then
                 b16(idx) := to_number(substr(b16(idx),1,1)) + to_number(substr(b16(idx),2,1));
            end if;  
        else 
            b16(idx) := trim(to_char(n16(idx)));
        end if;
    end loop;

    -- step 5) and 6) 
    for idx in 1..b16.count() loop
        if  b16(idx) not in ('A','B','C','D','E','F') then
            tot := tot + to_number(b16(idx));
        else
            tot := tot + n16(idx);
        end if;
    end loop;

    -- step 7) and 8)
    chk_digit := (ceil(tot/16)*16) - tot; 
    return chk_digit;
end;
/

To run:

select get_luhn_16_check_digit('0B012722900021AC35B2') from dual;

This now returns 1. Here is a LiveSQL demo.

---

There is still the question of what happens when the modulus 16 is greater than 9; for instance, this value returns 15.

select get_luhn_16_check_digit('22111111111111111111') from dual; 

A decimal result such as 15 is obviously not a check digit, which suggests you need an additional step 9). Perhaps we need to convert 15 -> (1+5) -> 6. Or Perhaps the digit should be base16? Please edit your question to confirm the appropriate rule.