Question 296618 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql function for Luhn algorithm to create the check digit NOT verify

This MySQL function should generate a check digit using Luhn algorithm. Given the number 345722 the function should return 3. It returns 6 which is wrong.

drop function if exists luhn;
delimiter //
  
create function luhn(myNum varchar(20))
    returns varchar(1)
    sql security invoker
begin
        
    declare sIndex, digit, len, parity,SumX int;
   
            set myNum=concat(myNum,'0');
            set len = length(myNum);
            set parity = len % 2;
            set SumX =0;
            set sIndex = len - 1;
 
    while sIndex >= 0 do
          set digit =  substring(myNum, sIndex,1);
             
            if(sIndex % 2 = parity) then set digit = digit * 2;
                end if;
                
                if(digit > 9) then set digit = digit - 9;
                 end if;
                
            set SumX = SumX + digit;
      set sIndex = sIndex - 1;
                
        end while;
                
                if SumX % 10 =0 then return 0;
                    else return (10 - (SumX % 10));
                end if;
         
      
end //

delimiter;

I converted the code above from the PHP code below which works.


function luhn_checksum($myNum) {
    $myNum = $myNum . "0";
    $len = strlen($myNum);
    $parity = $len % 2;
    $sum = 0;

    for ($index = $len - 1; $index >= 0; $index--) {
        $digit = intval($myNum[$index]);
        if ($index % 2 == $parity) {
            $digit *= 2;
        }
        if ($digit > 9) {
            $digit -= 9;
        }
        $sum += $digit;
       }
  
    if($sum % 10===0){return(0);}
   
        $finalCheckDigit= 10-($sum % 10);
            
    return($finalCheckDigit);
}

Given the number on the left I am expecting the middle number but getting the number on the right.

input   expect  returned
371950  7   5
367489  2   3
367457  9   1
350684  7   2
347772  6   1
345722  3   6
343464  4   3
339403  8   5
337069  9   4
331979  5   6
330500  0   6

I using this page to validate https://simplycalc.com/luhn-calculate.php

Tags:

2

Answers


  1. 0

    I tested this on MySQL 8.2 concurrent with the comments from other users above.

    create function luhn(myNum varchar(20))
returns int
deterministic
begin

  declare sIndex, digit, len, parity,SumX int;

  set myNum=concat(myNum,'0');
  set len = length(myNum);
  set parity = len % 2;
  set SumX =0;
  set sIndex = len-1;

  while sIndex >= 0 do
    -- CHANGE sIndex to sIndex+1:
    set digit = 0+substring(myNum, sIndex+1, 1);

    if sIndex % 2 = parity then
      set digit = digit * 2;
    end if;

    if digit > 9 then
      set digit = digit - 9;
    end if;

    set SumX = SumX + digit;
    set sIndex = sIndex - 1;

  end while;

  if SumX % 10 = 0 then
    return 0;
  else
    return 10 - (SumX % 10);
  end if;

end

    Test:

    select luhn('371950');
select luhn('367489');
select luhn('367457');
select luhn('350684');
select luhn('347772');
select luhn('345722');
select luhn('343464');
select luhn('339403');
select luhn('337069');
select luhn('331979');
select luhn('330500');

    Output:

    +----------------+
| luhn('371950') |
+----------------+
|              7 |
+----------------+

+----------------+
| luhn('367489') |
+----------------+
|              2 |
+----------------+

+----------------+
| luhn('367457') |
+----------------+
|              9 |
+----------------+

+----------------+
| luhn('350684') |
+----------------+
|              7 |
+----------------+

+----------------+
| luhn('347772') |
+----------------+
|              6 |
+----------------+

+----------------+
| luhn('345722') |
+----------------+
|              3 |
+----------------+

+----------------+
| luhn('343464') |
+----------------+
|              4 |
+----------------+

+----------------+
| luhn('339403') |
+----------------+
|              8 |
+----------------+

+----------------+
| luhn('337069') |
+----------------+
|              9 |
+----------------+

+----------------+
| luhn('331979') |
+----------------+
|              5 |
+----------------+

+----------------+
| luhn('330500') |
+----------------+
|              0 |
+----------------+
    Login or Signup to reply.
  2. 0

    Unless you are running your Luhn check-digit generation against a large dataset, these performance differences are not really relevant, but it has kept me entertained for a bit.

    Some small performance enhancements to Bill’s answer:

    1. There’s no need to subtract 1 from the length and then add it back on
    2. Using 0+ to implicitly cast to integer is unnecessary, as digit is declared as int
    3. Combining the multiple set statements into one is just a bit faster, albeit reducing readability
    create function luhn1(myNum varchar(20))
returns int
deterministic
begin

  declare sIndex, digit, parity, sumX int;

  set sIndex = length(myNum);
  set parity = sIndex % 2;
  set sumX = 0;

  while sIndex > 0 do

    set digit = substring(myNum, sIndex, 1);

    set sumX = sumX + if(sIndex % 2 = parity, if(digit * 2 > 9, (digit * 2) - 9, digit * 2), digit);
    set sIndex = sIndex - 1;

  end while;

  return (10 - (sumX % 10)) % 10;

end

    Here’s another approach without using a loop, which is a bit faster:

    create function `luhn2`(myNum varchar(20))
returns int
deterministic
begin

  set myNum = lpad(myNum, 20, '0');

  return (10 - ((
    + cast(substring(myNum, 1, 1) as unsigned)
    + 2 * cast(substring(myNum, 2, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 2, 1) as unsigned) % 10
    + cast(substring(myNum, 3, 1) as unsigned)
    + 2 * cast(substring(myNum, 4, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 4, 1) as unsigned) % 10
    + cast(substring(myNum, 5, 1) as unsigned)
    + 2 * cast(substring(myNum, 6, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 6, 1) as unsigned) % 10
    + cast(substring(myNum, 7, 1) as unsigned)
    + 2 * cast(substring(myNum, 8, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 8, 1) as unsigned) % 10
    + cast(substring(myNum, 9, 1) as unsigned)
    + 2 * cast(substring(myNum, 10, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 10, 1) as unsigned) % 10
    + cast(substring(myNum, 11, 1) as unsigned)
    + 2 * cast(substring(myNum, 12, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 12, 1) as unsigned) % 10
    + cast(substring(myNum, 13, 1) as unsigned)
    + 2 * cast(substring(myNum, 14, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 14, 1) as unsigned) % 10
    + cast(substring(myNum, 15, 1) as unsigned)
    + 2 * cast(substring(myNum, 16, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 16, 1) as unsigned) % 10
    + cast(substring(myNum, 17, 1) as unsigned)
    + 2 * cast(substring(myNum, 18, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 18, 1) as unsigned) % 10
    + cast(substring(myNum, 19, 1) as unsigned)
    + 2 * cast(substring(myNum, 20, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 20, 1) as unsigned) % 10
  ) % 10)) % 10;

end

    And here’s another version, specifically optimised for an input of 6 chars:

    create function `luhn3`(myNum varchar(6))
returns int
deterministic
begin

  if length(myNum) <> 6 then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Argument must be a string of 6 digits';
  end if;

  return (10 - ((
    + cast(substring(myNum, 1, 1) as unsigned)
    + 2 * cast(substring(myNum, 2, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 2, 1) as unsigned) % 10
    + cast(substring(myNum, 3, 1) as unsigned)
    + 2 * cast(substring(myNum, 4, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 4, 1) as unsigned) % 10
    + cast(substring(myNum, 5, 1) as unsigned)
    + 2 * cast(substring(myNum, 6, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 6, 1) as unsigned) % 10
  ) % 10)) % 10;

end

    Unsurprisingly, they all produce the same output:

    WITH toCheck (num) AS (
    VALUES
        ROW(371950), ROW(367489), ROW(367457), ROW(350684), ROW(347772), ROW(345722),
        ROW(343464), ROW(339403), ROW(337069), ROW(331979), ROW(330500)
)
SELECT num, luhnBK(num), luhn1(num), luhn2(num), luhn3(num) FROM toCheck;
    num luhnBK(num) luhn1(num) luhn2(num) luhn3(num)
    371950 7 7 7 7
    367489 2 2 2 2
    367457 9 9 9 9
    350684 7 7 7 7
    347772 6 6 6 6
    345722 3 3 3 3
    343464 4 4 4 4
    339403 8 8 8 8
    337069 9 9 9 9
    331979 5 5 5 5
    330500 0 0 0 0

    Using benchmark to run 1M iterations, I got the following execution times:

    mysql> SELECT BENCHMARK(1000000, luhnBK('371950'));
+--------------------------------------+
| BENCHMARK(1000000, luhnBK('371950')) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (35.93 sec)

mysql> SELECT BENCHMARK(1000000, luhn1('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn1('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (22.38 sec)

mysql> SELECT BENCHMARK(1000000, luhn2('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn2('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (9.43 sec)

mysql> SELECT BENCHMARK(1000000, luhn3('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn3('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (4.14 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search