# Interesting validation rules

Clone this wiki locally

# 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 )
{

\$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');
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]```