The Luhn Algorithm in SQL

Comments 1

Share to social media

The Luhn test is used by most credit card companies to check the basic validity of a credit card number. It is not an anti-fraud measure but a quick check on data corruption. It still allows any digits that are odd or even to be switched in the sequence. Most credit cards are compatible with Luhn algorithm.

It is often applied to SSNs, company organization numbers, and OCR numbers for internet payments. The algorithm is simple.

  • Take out the spaces from the string containing the credit card numbers
  • Reverse the string containing the credit card numbers.
  • Sum every digit whose order in the sequence is an odd number (1,3,5,7 …) to create a partial sum s1
  • Multiply each even digit by two, and then sum the digits of the number if the answer is greater than nine. (e,g if digit is 8 then 8*2=16, then add the resulting digits: 1+6=7).
  • Sum the partial sums of the even digits to form s2

if s1 + s2 ends in zero then the original number is in the form of a valid credit card number as verified by the Luhn test.

There are many ways of doing it in SQL. (and Rosetta Code is a good place to view solutions in various other languages). I believe that Peter Larsson holds the record for the fastest calculation of the Luhn test for a sixteen-digit credit card, with this code. As it stands, it isn’t a general solution, but it can be modified for different lengths of bank card.

Credit card numbers in general can have anywhere between eleven and twenty digits, depending on the provider. You can, of course, make Peter’s code more generic, though I suspect he’s come up with something even more blindingly fast.

This works because 0 digits do not affect the test, and you can merely assume that all strings are 20 characters long and zero-pad strings. If strings are odd in length (11,13,15 etc) then you can prepend a zero without affecting the checksum.

OK. So here is a pretty good solution that one can benchmark your own solution against. You would, of course, need to make sure that your solution actually can distinguish bogus from real cards. Here is a temporary table of various test cards and some bogus cards made by changing a few digits. Your solution must be able to correctly find the bogus cards. We can then check whether you have mistaken a bogus card for a good one and vice versa.

So there you have it: a couple of SQL implementations of the algorithm. A spoiler alert is that they run at roughly the same speed but Peter’s version seems to have the edge in my timings. Can you think of a quicker, or more elegant, way of doing it?