TSQL - Large Number Datatypes - with mod function

How do I take a very very large number like: 4.7119E+219 and return the Mod?
The table will hold the value of 4.7119E+219 but when I actually try to use it in Query Analyzer it get the error below.
How can I use the mod function with very very large numbers?

select 4.7119E+219%7


Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals modulo, type equals float.

The utility of this is using prime numbers to identify by factors the related records of a primary table.
michaelrobertfrenchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
have you tried
select convert(bigint,4.7119E+219)%7
jwinkler81Commented:
Don't think you'll be able to do this...

http://msdn.microsoft.com/library/en-us/tsqlref/ts_operator_5c1l.asp

This states that both the dividend and the divisor must be "any valid Microsoft® SQL Server™ expression of the integer data type category".

I think bigint might even qualify in that statement, however your number is far too large for a big int: -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)
Scott PletcherSenior DBACommented:
You might want to try the "Math & Science" topic area, explaining the restrictions imposed by SQL, of course.  There might be some mathematical "trick" that can be used to get the result.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

DabasCommented:
Following Scott's idea:
Essentially your number 4.7119E+219 is a 47119 followed by 216? zeros.
Lets simplify a bit, and assume it is 47119000
47119 mod 7 = 2
So essentailly the result of 47119000 mod 7 should equal 2000 mod 7 = 5
Something similar can be developed for a large number such as 4.7119E+219

Or... am I following wrong logics here.

Dabas (Retired mathematician and too lazy to check if his theory is mathematically correct)
Steven_WCommented:
Just a quick check ..

This (if it worked) would actually calculate :

4,711,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 MOD 7
  -- 47119 + (219-4 zeroes)

(and return a number between 0 and 6.

----

Using the pattern

select (47119*cast(1 as bigint))  % 7 -- 2
select (47119*cast(10 as bigint))  % 7 -- 6
select (47119*cast(100 as bigint))  % 7 --4
select (47119*cast(1000 as bigint))  % 7 -- 5
select (47119*cast(10000 as bigint))  % 7 --1
select (47119*cast(100000 as bigint))  % 7 -- 3
select (47119*cast(1000000 as bigint))  % 7 -- 2
select (47119*cast(10000000 as bigint))  % 7 -- 6
select (47119*cast(100000000 as bigint))  % 7 -- 4

you can identify that:
    (47119 * 10^x) === (47119 * 10^(x % 6 ))

This leads to the item that
    (47119 * 10^215) === (47119 * 10^3 )

So:
    select (47119*cast(100000 as bigint))  % 7 -- = 3

---

Caveats
   1- this doesn't answer your question (How to do it in SQL)

       That would be:
          declare @a varchar(500)
          set @a = 'select ( cast(4.7119E+'+cast((215 % 6) as varchar)+' as int) % 7 )'
          print @a
          exec (@a)

  2 - I am tired - my maths may be wrong

   3- This is probably not what you REALLY wanted.

Steven_WCommented:
Oh, and
    select (47119*cast(100000 as bigint))  % 7 -- = 3
Should have been:
    select (4.7119*cast(100000 as bigint))  % 7 -- = 6

:-)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
derobyCommented:
I'm not even sure what datatype you should store this kind of numbers in. I tried a float and it seems to work, but I'm not sure how correct this all is. Don't think SQL was designed with this kind of requirements in mind =)

Anyway, this following seems to work... but I wouldn't care too much for it's result because of all the rounding going on. Still it seemed like a funny challenge and this is the best I came up with. so far =)

DECLARE @amount float
DECLARE @divider float
DECLARE @test float
DECLARE @jumps float

SET @amount  = 4.7119E+219
SET @divider = 7
SET @jumps   = 2 -- I _assume_ @jumps needs to be smaller than @divider

WHILE @amount >= @divider
    BEGIN
        SET @test = @divider
        WHILE @amount >= @test
            SET @test = @test * @jumps

        SET @amount = @amount - (@test / @jumps)

    END

SELECT remainder = @amount
Steven_WCommented:
A float is an inprecise datatype.

It means that there is no difference between (4E+200) and (4E+200) + 1 and (4E+200) + 2

Yur extract above would not work

If you run the following:

-----

DECLARE @amount float
DECLARE @divider float
DECLARE @test float
DECLARE @jumps float

SET @amount  = 4.7119E+219
SET @divider = 7
SET @jumps   = 2 -- I _assume_ @jumps needs to be smaller than @divider

WHILE @amount >= @divider
   BEGIN
            SET @test = @divider
            WHILE @amount >= @test
               BEGIN
                        SET @test = @test * @jumps
                        print ('Test = ' + cast(@test as varchar(100)))
                  END
            
            SET @amount = @amount - (@test / @jumps)
            print ('Amount = ' + cast(@Amount as varchar(100)))
      END

SELECT remainder = @amount

-----

You'll see that is calculating:
    917504 * 2 = 1.83501e+006
    or : 917504*7 = 1835010 ... In fact, the answer is REALLY 1835008

So, this isn't really achieveing your desired result ..

I don't guarantee that my method is any better (or even correct), :-)

---

However, the following may be useful:
   http://nrich.maths.org/public/viewer.php?obj_id=373&part=solution&refpage=viewer.php

derobyCommented:
Steven_W: indeed, like I said : don't trust it's result too much =)

The only way around this would seem to be by setting @jump to 10 => that way the E-notation would be correct (we simply add 1 to the number behind the E), but I have no knowledge on how that behaves internally and wether or not there is rounding 'somehow' anyway.

Running it with @jump = 10 indeed seems to be 'more correct' judging by the intermediate results, but now we risk getting a remainder that's bigger than the divider.
Probably we'll then need to make the last line :

SELECT remainder = Convert(int, @amount) % Convert(int, @divider)

But again, I'm still not a big believer in the result =) But it's fun theorizing about it, and it does "feel" a bit more correct than the previous version though =P
(and it's actaully pretty quick too, my first attemp was simply substracting the @divider from the @amount. Clearly that didn't work out =)
Steven_WCommented:
Oops :-)

Repeatedly subtracting 7 from 4,711,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 could leve you sitting there for a LONG time :-)

When we're talking about numbers as big as 47 million million million googol googols ( or something marginally smaller than 130 factorial ) , then this problem become somewhat unorthodox for standard data-structures to manipulate .

derobyCommented:
Not only I assumed to be sitting there for quite a while, I actually was going to time it !
After a couple of minutes it dawned on me that 2.79e+239 - 7 would result in exatly the same 2.79e+239, thus the loop would go on and on and on =)

But you're right, I too very much doubt doing this outside specialized math libraries is unlikely to work, at least not reliably, which brings me back to my first thought : there must be better ways to do this =)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.