From Ingres Community Wiki

References

Check Digit Functions

Ability to generate a checkdigit and to test if the checkdigit is valid.

• generate_digit('scheme', 'string')...Which returns the check digit as a varchar(2) item. Although in most cases a check digit is a single character, there are encoding schemes where two character check digits are created. These schemes are not supported in this version, but the varchar(2) allows the expansion when required.

• validate_digit('scheme', 'string') … which returns 1 for valid 0 for invalid.

In both cases the scheme may be one of:

• luhn, luhn_a
• verhoeff, verhoeffnr
• ISBN, ISBN_13
• ISSN
• UPC (aka: UPC_A, EAN_12), UPC_E.
• EAN(aka: EAN_13, GTIN_13, JAN)
• EAN_8 (aka: GTIN_8)

Note that the luhn and verhoeff schemes can function on a character string of any length and no restriction is made by the function. That is, if you wish to generate a luhn digit for a string of 1024 characters then you may...whether or not that's a useful exercise is upto you.

The other schemes are 'fixed length' schemes and can only be applied to a string of the correct length. An error will be generated if a string of incorrect length string is supplied.

luhn/luhn_a

Refer To: http://en.wikipedia.org/wiki/Luhn_algorithm

Created by IBM scientist Hans Peter Luhn. Most credit cards and many government identification numbers are based on this simple algorithm.

For example Credit Card numbers are an implementation of the Luhn Algorithm, but the number is restricted to the range of 13 to 19 characters.

For normal 'luhn' numbers. the string may be of any length, but must be composed entirely of digits.

The 'luhn_a' scheme is an extension that allows for alpha's by assiging the number 10 to 'A', 11 to 'B' and so on. It is case insensitive. For example, the Committee on Uniform Security Identification Procedures (CUSIP) provides a 9-character alphanumeric string based on this algorithm.

verhoeff/verhoeffnr

These implement the Verhoeff Algorithm.

Refer To: http://en.wikipedia.org/wiki/Verhoeff_Algorithm.

         http://www.augustana.ab.ca/~mohrj/algorithms/checkdigit.html

The Verhoeff algorithm was developed by Dutch Mathematician Jacobus Verhoeff. This algorithm detects all transposition errors as well as other types of error that would not be detected by the Luhn Algorithm. The algorithm is based on the properties of a non-commutative system of operations on ten elements.

The Verhoeff Algorithm, like the Luhn algorithm can also handle strings of any length. But the strings must be composed entirely of digits.

NOTE: The algorithm described in wikipedia, implemented here and corroborated by the augustana site differs from that found in 'Numerical Recipes in C'. Hence I have implemented verhoeffNR which is the verhoeff algorith as published in Numerical Recipes.

ISBN

ISBN International Standard Book Number

ISBN_13 International Standard Book Number - 13 character version.

Refer To: http://en.wikipedia.org/wiki/ISBN

The standard ISBN is a 10 character numeric identifier for printed material. Since 1st January, 2007, the ISBN have been 13 digits. Note that these are compatible with EAN_13.

The check digit may be either a digit or the alpha 'X'. For example:

• 99921-58-10-7
• 9971-5-0210-0
• 0-8044-2957-X

ISSN

ISSN International Standard Serial Number

Refer To: http://en.wikipedia.org/wiki/ISSN

ISSNs are 8 digit numbers used to identify electronic or print periodicals. Example: The Journal of Hearing Research: 03785595

UPC

UPC: Universal Product Code

Refer to: http://en.wikipedia.org/wiki/Universal_Product_Code

Used to identify products to bar code readers. UPC or UPC_A encodes 12 digits. Note that a UPC_A number is also an EAN_12 number. Furthermore if the UPC_A is prefixed with a '0' it becomes an EAN_13.

UPC_E encodes 6 letters and were intended for smaller packages where a full size UPC_A would be cumbersome. Unlike the UPC_A the check digit is not appended to the number. Rather, it is used to determine the 'odd/even' parity assigned to the numbers for when they are encoded as bar-code lines.

EAN

EAN: European Article Number

Refer to: http://en.wikipedia.org/wiki/EAN-13

The EAN is a barcoding standard which is a superset of the original 12-digit Universal Product Code (UPC) system developed in North America. The EAN_13 barcode is defined by the standards organisation GS1. It is also called a Japanese Article Number (JAN) in Japan. UPC, EAN, and JAN numbers are collectively called Global Trade Item Numbers (GTIN), though they can be expressed in different types of barcodes.

The EAN_13 barcodes are used worldwide for marking retail goods. The less commonly used EAN_8 barcodes are used also for marking retail goods;

Ingres Enhancement Number

SIR: 121969

Notes

Extent of Changes

There are a plethora of various check_digit functions used around the world. A simple scan of wikipedia can verify that. Many of these seem extremely specific and for example, it would be hard to justify including the code necessary for the check digit calculation behind the Australian Tax File Number...worthy as that system is!

Instead, I suggest we limit this to genuinely widespread cases such as Verhoeff and Luhn.

Possible Additions

I've got the OME code for (amongst others) Vehicle Identification Numbers. If there is a show of support for their inclusion I'll convert these to Opensource Code for inclusion.

Verhoeff issues

There seems to be two opinions on the Verhoeff Algorithm. The version in wikipedia (http://en.wikipedia.org/wiki/Verhoeff_Algorithm) and the version as published in "Numerical Recipes in C". These do give different answers.

The wikipedia version is also supported by: http://www.augustana.ab.ca/~mohrj/algorithms/checkdigit.html

Unless otherwise instructed I'll code up both versions as 'Verhoeff' and 'VerhoeffNR'. The latter being the Numeric Recipes version.

Bug/SIR Reference

SIR 121969

DDS

DDS is available here

Diff file

The Diffs are available here: diff.20090624

The tests on the diffs from here: Marty_log.zip

Test scripts

A zipfile of the entire test suite is available at: Test_programs.zip

Documentation

Check Digit Functions

Two SQL functions are used to generate and valid a check digit.

The function GENERATE_DIGIT generates a check digit for a specified string. The check digit can be used to help determine if a string, such as a credit card number, has been entered correctly.

The function VALIDATE_DIGIT tests if the check digit is valid.

Syntax for these functions is:

GENERATE_DIGIT('scheme', 'string')

which returns the check digit as a character.

VALIDATE_DIGIT('scheme', 'string')

which returns 1 for valid, 0 for invalid.

The scheme can be one of the following check digit schemes:

• LUHN or LUHN_A
• VERHOEFF or VERHOEFFNR
• ISBN
• ISBN_13
• ISSN
• UPC, UPC_A, or EAN_12
• UPC_E
• EAN, EAN_13, GTIN_13, or JAN
• EAN_8 or GTIN_8