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.

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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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,80

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)

This (if it worked) would actually calculate :

4,711,900,000,000,000,000,

-- 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.

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 trialAnyway, 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

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

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 =)

Repeatedly subtracting 7 from 4,711,900,000,000,000,000,

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 .

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 =)

Microsoft SQL Server

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

select convert(bigint,4.7119E+219