UID Validation - Verhoeff's Algorithm

Question

I am using MS Access & SQL.

I wish to validate 0.1 Mn UID Numbers (Aadhar Cards) for their authenticity.

The UID is based on Verhoeff's Algorithm.

I did find some queries online. They pertained to C++/Java.

Is there a query which validates 0.1 Mn in MS Access for SQL?

Answer 1

As mentioned by Gord Thempson in the comments, you can find the VBA (and many more implementation on the Wikipedia page

For the sake of completeness,

''' <summary>
''' For more information cf. http://en.wikipedia.org/wiki/Verhoeff_algorithm
''' Dihedral Group: http://mathworld.wolfram.com/DihedralGroup.html
''' You can use this code in Excel, Access, etc...
''' </summary>
''' <remarks></remarks>

'The multiplication table
Dim d(0 To 9) As Variant
'The permutation table
Dim p(0 To 8) As Variant
'The inverse table
Dim inv(0 To 9) As Integer

Private Sub initVerhoeffConsts()
    If IsArray(d(0)) Then Exit Sub 'Shortcut if already initiated
    d(0) = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
    d(1) = Array(1, 2, 3, 4, 0, 6, 7, 8, 9, 5)
    d(2) = Array(2, 3, 4, 0, 1, 7, 8, 9, 5, 6)
    d(3) = Array(3, 4, 0, 1, 2, 8, 9, 5, 6, 7)
    d(4) = Array(4, 0, 1, 2, 3, 9, 5, 6, 7, 8)
    d(5) = Array(5, 9, 8, 7, 6, 0, 4, 3, 2, 1)
    d(6) = Array(6, 5, 9, 8, 7, 1, 0, 4, 3, 2)
    d(7) = Array(7, 6, 5, 9, 8, 2, 1, 0, 4, 3)
    d(8) = Array(8, 7, 6, 5, 9, 3, 2, 1, 0, 4)
    d(9) = Array(9, 8, 7, 6, 5, 4, 3, 2, 1, 0)

    p(0) = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
    p(1) = Array(1, 5, 7, 6, 2, 8, 3, 0, 9, 4)
    p(2) = Array(5, 8, 0, 3, 7, 9, 6, 1, 4, 2)
    p(3) = Array(8, 9, 1, 6, 0, 4, 3, 5, 2, 7)
    p(4) = Array(9, 4, 5, 3, 1, 2, 6, 8, 7, 0)
    p(5) = Array(4, 2, 8, 6, 5, 7, 3, 9, 0, 1)
    p(6) = Array(2, 7, 9, 3, 8, 0, 6, 4, 1, 5)
    p(7) = Array(7, 0, 4, 6, 9, 1, 3, 2, 5, 8)

    inv(0) = 0: inv(1) = 4: inv(2) = 3: inv(3) = 2: inv(4) = 1
    inv(5) = 5: inv(6) = 6: inv(7) = 7: inv(8) = 8: inv(9) = 9
End Sub


''' <summary>
''' Validates that an entered number is Verhoeff compliant.
''' </summary>
''' <param name="num"></param>
''' <returns>True if Verhoeff compliant, otherwise false</returns>
''' <remarks>Make sure the check digit is the last one!</remarks>
Public Function validateVerhoeff(ByVal num As String) As Boolean
        initVerhoeffConsts
        Dim c As Integer
        Dim i As Integer
        c = 0
        Dim myArray() As Integer
        myArray = StringToReversedIntArray(num)

        For i = 0 To UBound(myArray)
            c = d(c)(p((i Mod 8))(myArray(i)))  'Version corrected by WHG gives error
        Next i

        validateVerhoeff = (c = 0)
End Function


''' <summary>
''' For a given number generates a Verhoeff digit
''' </summary>
''' <param name="num"></param>
''' <returns>Verhoeff check digit as Integer</returns>
''' <remarks>Append this check digit to num</remarks>
Public Function generateVerhoeff(ByVal num As String) As Integer
    initVerhoeffConsts
    Dim c As Integer
    Dim i As Integer    
    c = 0
    Dim myArray() As Integer
    myArray = StringToReversedIntArray(num)

    For i = 0 To UBound(myArray)
        c = d(c)(p((i + 1) Mod 8)(myArray(i)))   'Version corrected by WHG gives error in compilation
    Next i

    generateVerhoeff = inv(c) 'str(inv(c))
End Function


''' <summary>
''' Converts a string to a reversed integer array.
''' </summary>
''' <param name="str"></param>
''' <returns>Reversed integer array</returns>
''' <remarks></remarks>
Private Function StringToReversedIntArray(ByVal str As String) As Integer()
    Dim lg As Integer
    lg = Len(str)
    Dim myArray() As Integer
    ReDim myArray(0 To lg - 1)
    Dim i As Integer

    For i = 0 To lg - 1
        myArray(i) = AscW(Mid$(str, lg - i, 1)) - AscW("0")
    Next
    StringToReversedIntArray = myArray
End Function

''' In Excel don't copy this sub _AssertsVerhoeff()as get a compilation error. 4/21/2013
Public Sub _AssertsVerhoeff()
    Debug.Print "Start Verhoeff's Asserts"
    Debug.Assert generateVerhoeff("75872") = 2
    Debug.Assert validateVerhoeff("758722") = True
    Debug.Assert generateVerhoeff("12345") = 1
    Debug.Assert validateVerhoeff("123451") = True
    Debug.Assert generateVerhoeff("142857") = 0
    Debug.Assert validateVerhoeff("1428570") = True
    Debug.Assert generateVerhoeff("123456789012") = 0
    Debug.Assert validateVerhoeff("1234567890120") = True
    Debug.Assert generateVerhoeff("8473643095483728456789") = 2
    Debug.Assert validateVerhoeff("84736430954837284567892") = True
    Debug.Assert generateVerhoeff("12345") = 1
    Debug.Assert validateVerhoeff("123451") = True
    Debug.Assert validateVerhoeff("124351") = False
    Debug.Assert validateVerhoeff("122451") = False
    Debug.Assert validateVerhoeff("128451") = False
    Debug.Assert validateVerhoeff("214315") = False
    Debug.Print "End Verhoeff's Asserts"
End Sub