Unique Learner Number (ULN) Validation in SQL

ulnValidation.sql

/* 
 * verifies checksum of ULN 
 * from https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/710270/ULN_validation.pdf
 */

SELECT 
	uln_number,
    SUBSTRING(uln_number, 1, 9) AS ExtractString,
    RIGHT(uln_number, 1) AS givenChecksum,
    10 - MOD(SUBSTRING(uln_number, 1, 1) * 10 +
    SUBSTRING(uln_number, 2, 1) * 9 +
    SUBSTRING(uln_number, 3, 1) * 8 +
    SUBSTRING(uln_number, 4, 1) * 7 +
    SUBSTRING(uln_number, 5, 1) * 6 +
    SUBSTRING(uln_number, 6, 1) * 5 +
    SUBSTRING(uln_number, 7, 1) * 4 +
    SUBSTRING(uln_number, 8, 1) * 3 +
    SUBSTRING(uln_number, 9, 1) * 2, 11) as CalculatedChecksum,
    if( 10 - MOD(SUBSTRING(uln_number, 1, 1) * 10 +
    SUBSTRING(uln_number, 2, 1) * 9 +
    SUBSTRING(uln_number, 3, 1) * 8 +
    SUBSTRING(uln_number, 4, 1) * 7 +
    SUBSTRING(uln_number, 5, 1) * 6 +
    SUBSTRING(uln_number, 6, 1) * 5 +
    SUBSTRING(uln_number, 7, 1) * 4 +
    SUBSTRING(uln_number, 8, 1) * 3 +
    SUBSTRING(uln_number, 9, 1) * 2, 11) = RIGHT(uln_number, 1), 'Valid', 'Invalid') as result
FROM
    records;