Generate Luhn Mod N using SQL

Question

We are using CRYPT_GEN_RANDOM to generate a 15 character string (numeric plus uppercase = 36 options for each character), and would like to add the final character as a checksum using Luhn Mod N.

I am looking for a way to do this using SQL. There are many examples for the standard luhn formula which uses only digits, but I could not find one for Luhn Mod N.

Answer 1

when i have interpreted the formula for Luhn mod N correct:

CREATE DEFINER=`root`@`localhost` FUNCTION `LUHN_MOD_N`(input CHAR(15)) RETURNS char(1) CHARSET ascii
    DETERMINISTIC
BEGIN

-- https://stackoverflow.com/questions/59471115/generate-luhn-mod-n-using-sql
set @retValue =' ';

with 
  recursive codePointFromchar as (
     select 'A' cp, 0 as val
     union all
     select CHAR(ASCII('A' )+val+1), val+1 
     from codePointFromchar 
     where val < 26 
  ), 
  cte as (
    select input as x, 2 as factor, 0 as sum, 0 as codePoint
    union all
    select 
       x, 
       case when factor=2 then 1 else 2 end, 
       sum+((floor((select val from codePointFromchar where cp =MID(x,codePoint+1,1) limit 1)/15) +
             ((select val from codePointFromchar where cp =MID(x,codePoint+1,1) limit 1) % 15)
            )* factor ), 
       codePoint+1 
    from cte where codePoint<15
)
select (select cp from codePointFromchar where val = ((15 - sum%15) % 15) ) into @retValue 
        from cte order by codePoint desc limit 1 ;

return @retValue;
END

• The value for N is set fixed to 15.
• The input value ABCDEFGHIJKLMNO is currently also fixed.
• The cte for codePointFromchar assigns codePoint values to the characters.

EDIT: changed SQL to a FUNCTION.

output:

mysql> SELECT LUHN_MOD_N('ABCDEFGHIJKLMNO');
+-------------------------------+
| LUHN_MOD_N('ABCDEFGHIJKLMNO') |
+-------------------------------+
| E                             |
+-------------------------------+