Skip to Main Content
  • Questions

  • How to perform mod for a numeric value having more than 40 characters

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Eelco.

Asked: July 18, 2013 - 12:29 pm UTC

Last updated: July 19, 2013 - 2:31 pm UTC

Version: 10G

Viewed 1000+ times

You Asked

For SEPA (single euro payment area) we like to perform a check if an IBAN (bank account number is correct).
Part of the IBAN number is a check digit which is euqal to 98 - MOD(X,97).
Some countries have an IBAN resulting in X having more than 40 characters.
It looks like the function MOD wont work when X > 38 characters (actually it does not run in error, it just returns 0 in any case).

Any idea how to solve this?

test:
SELECT MOD(221021290110000123452229211210282900128222900,97)
FROM DUAL
=> 0 ??


Many thanks in advance.
Eelco.

and Tom said...

that is because we only support 38 to 39 digits of precision. Your number ends in "0" as far as we are concerned:


ops$tkyte%ORA11GR2> set numformat 9999999999999999999999999999999999999999999999999
ops$tkyte%ORA11GR2> select 221021290110000123452229211210282900128222902 from dual;

     221021290110000123452229211210282900128222902
--------------------------------------------------
     221021290110000123452229211210282900128000000


you cannot put numbers with more than 38 significant digits into an Oracle number - you'll lose some of that precision.


Using the logic gleaned from this page:
http://www.devx.com/tips/Tip/39012

I came up with this:

ops$tkyte%ORA11GR2> select mod( to_number( mod( to_number( substr( str, 1, 30 ) ), 97 ) || substr( str, 31 ) ), 97 ) m
  2    from (
  3  select '221021290110000123452229211210282900128222902' str
  4    from dual
  5         )
  6  /

                                                 M
--------------------------------------------------
                                                16


basically - take the first 30 digits of your number, mod it, concatenate that back onto the rest of the number - which should be small enough to be an Oracle number with full precision, and mod that...


I tested a few values with:

http://www.calculatorpro.com/calculator/modulo-calculator/

which does very large modulo's for us and it seems to work. Please do a little more research on the math behind this to be comfortable with the approach yourself.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

math behind that is ok

Matthias Rogel, July 19, 2013 - 4:01 pm UTC

... , since calculating in moduli is calculating in a ring, see http://en.wikipedia.org/wiki/Ring_%28mathematics%29 

sokrates@12.1 > with function big_mod ( large_int in varchar2, n in int ) return int as
begin
  return
     case when length( large_int ) > 10 then
        big_mod ( large_int => mod( to_number( substr( large_int, 1, 10 ) ), n) || substr(large_int, 11), n => n)
     else
        mod( to_number( large_int ), n )
     end;
end big_mod;
select big_mod('123456789033333333335555555555', 320) from dual
/
  2    3    4    5    6    7    8    9   10   11  
BIG_MOD('123456789033333333335555555555',320)
---------------------------------------------
        35


nice !

A reader, October 05, 2018 - 7:30 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.