MySQL Automatically Calculate IBAN bigint overflow

Question

this is my first question I am posting in stackoverflow, I hope, someone can help me. So far I could not find anyone asking a similar question here.

I would like to check whether a German IBAN is correct. In principle, this is easy: You concatenate the Bank_number (8 digits) with the Account_Number (10 digits), the number equivalent to DE (1314 = 4 digits) and a check number (2 digits), then you calculate the modulo division and the result should be 1.

Here is the code I enter into MySQL:

SELECT MOD(500105175404777100131451,97) as a
     , MOD(CONVERT(CONCAT("50010517","5404777100","1314","51"), UNSIGNED),97) as b
     , CONVERT(CONCAT("50010517","5404777100","1314","51"), UNSIGNED) as c

And here is the result I get from MySQL:

a   b    c
1   60   18446744073709551615

As you can see, when I enter the entire number manually, I get the correct result (1), but when I try to construct the number from the separate strings (in this format they are so far saved in the database), the modulo division does not work because the string is incorrectly transformed to a number.

Can anyone tell me what the problem could be?

I tried "BIGINT" instead of "UNSIGNED" but then got the error

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB
server version for the right syntax to use near 'BIGINT"

Any help would greatly be appreciated.

Thanks Stephan

Answer 1

Use the DECIMAL(24) numeric type.

SELECT MOD(500105175404777100131451,97) as a
     , MOD(CONVERT(CONCAT("50010517","5404777100","1314","51"), decimal(24)),97) as b
     , CONVERT(CONCAT("50010517","5404777100","1314","51"), decimal(24)) as c