University of Otago student ID numbers

Algorithm

1. Pad number with leading zeroes until it is seven digits long.
2. Multiply the first six digits by 4, 6, 3, 5, 2, 1, respectively and sum.
3. Mod by 11 then by 10, result is check digit ⇒ seventh digit of ID number.

Implementations

Pure SQL

Theoretically this could be done as a check constraint in plain SQL, but it’s pretty convoluted and probably not worth the effort. Lots of nested and replicated substring and cast (to number and string) function calls.

-- Oracle SQL syntax, in the context of a CHECK constraint:
    constraint Student_ID_Checksum check (
        substr(to_char(Student_ID, 'FM0000000'), 7, 1) =
        mod(
            mod(
                4 * substr(to_char(Student_ID, 'FM0000000'), 1, 1) +
                6 * substr(to_char(Student_ID, 'FM0000000'), 2, 1) +
                3 * substr(to_char(Student_ID, 'FM0000000'), 3, 1) +
                5 * substr(to_char(Student_ID, 'FM0000000'), 4, 1) +
                2 * substr(to_char(Student_ID, 'FM0000000'), 5, 1) +
                1 * substr(to_char(Student_ID, 'FM0000000'), 6, 1)
            , 11)
        , 10)
    )

-- PostgreSQL syntax (using standard SQL SUBSTRING and CAST expressions):
mod(
    mod(
        4 * cast(substring(to_char(Student_ID, 'FM0000000') from 1 for 1) as integer) +
        6 * cast(substring(to_char(Student_ID, 'FM0000000') from 2 for 1) as integer) +
        3 * cast(substring(to_char(Student_ID, 'FM0000000') from 3 for 1) as integer) +
        5 * cast(substring(to_char(Student_ID, 'FM0000000') from 4 for 1) as integer) +
        2 * cast(substring(to_char(Student_ID, 'FM0000000') from 5 for 1) as integer) +
        1 * cast(substring(to_char(Student_ID, 'FM0000000') from 6 for 1) as integer)
    , 11)
, 10)

PL/pgSQL

PL/pgSQL arrays are 1 indexed.

create or replace function Valid_Otago_Student_ID(in Student_ID integer) returns boolean as $$
declare
    -- weights are in reverse order because digits are numbered from right to left,
    -- i.e., digit 1 is the rightmost digit
    weights smallint[] := '{0, 1, 2, 5, 3, 6, 4}';
    computed_check_digit smallint := 0;
    student_check_digit smallint := Student_ID % 10;
    digit smallint;
begin
    for i in reverse 7..2 loop
        digit := trunc(Student_ID / 10.0 ^ (i - 1) % 10.0);
        computed_check_digit := computed_check_digit + digit * weights[i];
    end loop;
    computed_check_digit := computed_check_digit % 11 % 10;
    return student_check_digit = computed_check_digit;
end;
$$ language 'plpgsql';

This can be used in a trigger:

create or replace function Valid_Otago_Student_ID() returns trigger as $$
begin
    if Valid_Otago_Student_ID(new.Student_ID) then
        return new;
    else
        raise check_violation using message = 'Invalid student ID ' || new.Student_ID;
    end if;
end;
$$ language 'plpgsql';

create trigger Valid_Otago_Student_ID
  before insert or update of Student_ID on Student
  for each row execute procedure Valid_Otago_Student_ID();

PHP

// Should work with both strings and numbers; PHP data types are extremely fluid.
function validate_student_ID ( $student_id )
{
    $digits = str_split( str_pad( $student_id, 7, '0', STR_PAD_LEFT ) );

    $check_digit = ( ( $digits[0] * 4 ) +
                     ( $digits[1] * 6 ) +
                     ( $digits[2] * 3 ) +
                     ( $digits[3] * 5 ) +
                     ( $digits[4] * 2 ) +
                     ( $digits[5] * 1 )
                   ) % 11 % 10;

    return ( $check_digit == $digits[6] );
}

Java

Integer based

This will probably be faster overall, but the code is harder to follow if you don’t know how the maths works.

import java.lang.Math;

public class OtagoStudentId {
    // weights are in reverse order because digits are numbered from right to left,
    // i.e., digit 0 is the rightmost digit
    private static final Integer[] WEIGHTS = new Integer[]{0, 1, 2, 5, 3, 6, 4};

    public static Boolean validate(Integer studentId) {
        Integer computedCheckDigit = 0;
        Integer studentCheckDigit = studentId % 10;
        for (Integer i = 6; i > 0; i--) {
            Integer digit = (int)(studentId / Math.pow(10, i) % 10);
            computedCheckDigit += digit * WEIGHTS[i];
        }
        computedCheckDigit = computedCheckDigit % 11 % 10;
        return studentCheckDigit.equals(computedCheckDigit);
    }

    public static Boolean validate(String studentId) {
        return validate(new Integer(studentId));
    }
}

String based

This is easier to understand, but will probably be slower due to additional string jiggery-pokery.

public class OtagoStudentId {
    // digits are numbered from left to right, i.e., digit 0 is the leftmost digit
    private static final Integer[] WEIGHTS = new Integer[]{4, 6, 3, 5, 2, 1, 0};

    public static Boolean validate(String studentId) {
        Integer computedCheckDigit = 0;
        // left pad input with 0 up to length 7
        String paddedId = String.format("%1$7s", studentId).replace(' ', '0');
        Integer studentCheckDigit = Integer.parseInt(paddedId.substring(6));
        for (Integer i = 0; i < 6; i++) {
            Integer digit = Integer.parseInt(paddedId.substring(i, i + 1));
            computedCheckDigit += digit * WEIGHTS[i];
        }
        computedCheckDigit = computedCheckDigit % 11 % 10;
        return studentCheckDigit.equals(computedCheckDigit);
    }

    public static Boolean validate(Integer studentId) {
        return validate(String.valueOf(studentId));
    }
}

Python

def valid_id(id):
    digits = [int(i) for i in str(id).zfill(7)]
    multipliers = [4, 6, 3, 5, 2, 1]
    mdigits = [a*b for a,b in zip(digits[:6], multipliers)]
    return (sum(mdigits) % 11 % 10) == digits[6]