With the introduction of SEPA, the use of IBAN is now mandatory for all bank transactions in the Euro zone. This forces almost everyone to migrate their data, replacing national bank account numbers with IBANs. Fortunately this is a fairly simple task: to get the IBAN for an existing account, take the two-character country code, add two check digits, and then the national bank account number you already have. But how do you compute the check digits?

IBAN is based on a verification scheme called MOD 97-10. Don’t let the name scare you: what it means is that the codes are numeric, and have two check digits at the end. To verify the code you divide it by 97: if the remainder is 1 the code is valid, otherwise not. When generating the check digits you do the same computation with the check digits set to `00`

. Subtracting the remainder from 98 gives you the check digit. This works because if the code is C and its remainder is R, then the remainder computed for C+(98-R) is the same as for R+98-R = 98. The remainder of 98 when divided by 97 of course is 1. This is really straightforward to implement:

function mod_97_10_check($code) { return bcmod($code, 97) == 1; } function mod_97_10_digit($code) { return 98 - bcmod($code, 97); }

With IBAN you need some preprocessing though. First you have to move the country code and check digits to the right, and replace every letter with digits so that A becomes 10, B becomes 11, C becomes 12, and so on. Here’s a function to generate the IBAN check digits, given the country code and the national account number:

function iban_check_digit1($country, $account_number) { $code = $account_number . $country . '00'; $code = strtr($code, array('A'=>'10','B'=>'11', /* etc */)); return sprintf("%02d", mod_97_10_digit($code)); }

This is great for converting individual account numbers, but what if we have to migrate a whole database? You could write a script that updates every single record one by one, or you could write a single SQL query that does the same thing. In MySQL it’s convenient to first define a function that computes the MOD 97-10 check digits:

create function mod_97_10_check(s text) returns char(2) return lpad(98 - cast(s as decimal(60)) % 97, 2, '0');

For the rest, string manipulation is unfortunately not one of SQL’s strengths, so we’ll simplify a little. For example, in most countries bank account numbers contain only digits, so only the country code needs replacing and we can do that by by hand. For example, `ES`

for Spain is mapped to `1428`

, so this is how you could compute the IBAN check digit for Spanish “CCC” format account numbers:

UPDATE bank_account_numbers SET iban = concat('ES', mod_97_10_check(concat(ccc, '142800')), ccc);

The exact decimal arithmetic implemented in MySQL is really handy here. Without it we would have to create a stored procedure that computes the remainder 9 digits at a time, based on Horner’s rule.