The algorithm named Luhn 10 was invented to protect against accidental errors. The algorithm does NOT detect when digits are exchanged on odd numbered places or exchanged between even numbers places.
The Luhn10 algorithm (also known as modula 10) is widely used for credit card numbers, IMEI numbers, National Identifier numbers in multiple countries, etc.
What the algorithm does is to start from the last digit. If this digit is placed on an even-numbered place, this digit and all other digits on even-numbered places are multiplied by two. All digits on odd-numbered places are multiplied by 1. Vice versa, if the last digit is an odd-numbered place, all odd-numbered placed digits are multiplied by 2 and digits on even-numbered places are multiplied by 1.
Example: Credit card number 5142 4300 7652 4922 can be checked using
2 * 5 = 10
1 * 1 = 1
2 * 4 = 8
1 * 2 = 2
2 * 4 = 8
1 * 3 = 3
2 * 0 = 0
1 * 0 = 0
2 * 7 = 14
1 * 6 = 6
2 * 5 = 10
1 * 2 = 2
2 * 4 = 8
1 * 9 = 9
2 * 2 = 4 (last digit, always multiply by 2).
Now we take the sum of all products, with all digits applied separately.
1 + 0 + 1 + 8 + 2 + 8 + 3 + 0 + 0 + 1 + 4 + 6 + 1 + 0 + 2 + 8 + 9 + 4 = 58
If the sum is a multiple of 10, the check digit is 0. Otherwise the sum is withdrawn from nearest higher multiple of 10, so in this case we calculate 60 – 58 = 2.
As we can see, the check digit of 2 is the same last digit as in the original credit card number.
How can we implement this is SQL Server? Remember, the Luhn10 algorithm can be applied to a string of any length.
This is one very efficient way. You can replace the numbers table with your preferred choice of numbers table.
You can even make a cte (or derived table) with the range of numbers you expect to use.
I have made some intelligent replacements to the Luhn10 algorithm to have it better executed in SQL Server.
To make the function feature-complete, such as returning NULL on invalid input, use this
