Based on the specific examples we’ll provide, you should ultimately be able to apply the techniques we will use in the event that you need to calculate a check digit from a different algorithm using T-SQL.All of the check digit calculation routines we’ll present use set-based T-SQL code, so should perform better than an equivalent looping solution (although we will not attempt to prove this).
First we’ll mention a few common, human-keying errors that can occur, for which check digits were invented to avoid.
Numerical methods exist that can generate all of the possible transcription errors of the types described for a given length string, and these can then be assembled into test strings to be applied against the check digit algorithm.
From this, you can arrive at a probability distribution that describes how efficient the algorithm is in avoiding these transcription errors. Most check digit calculation algorithms require applying some transformation to each digit in a string.
In T-SQL, it is relatively awkward to do this directly to each character in a string, particularly when the string is not of fixed length.
As part of the routine data cleansing of such codes on data entry we must check that the code is valid- but do we?
Dwain Camps shows how it can be done in SQL in such a way that it could even be used in a constraint, to keep bad data out of the database.
Things like the product code on our box of cereal, many national identity numbers, international bank account numbers and even the vehicle identification number on our personal cars all have a check digit embedded somewhere within that long string of digits and characters.
The idea of including a check digit in a number was invented to avoid common human transcription errors that easily occur when keying from a document into a data processing application. If someone is keying in your bank account number so they can send you some money, wouldn’t you like to have some level of confidence that they got that number correct?
Check digits have become ubiquitous in the digital age.
Oftentimes we may not realize that the numbers that occur in many real-life situations contain a check digit.
Fortunately for us, since I am no mathematician, we will focus on the more mundane aspect of calculating check digits using some known, good algorithms that have been invented by those much smarter than me.