Modulo, float number

How to use modulo for float numbers? For example, how to find the result of

select power(cast(101 as fl开发者_JAVA百科oat),50)%221

There are situations where one might need to use modulo on large numbers, and one way to do this is using decimal (since int and bigint might be too small).

You have to implement modulo operator yourself - one way is this:

SELECT (x/y-ROUND(x/y,0,1))*y

ROUND(x/y,0,1) function returns truncated x/y. For example,

(10.0/3 - ROUND(10.0/3,0,1))*3 = (3.333333-3)*3 = 1 

This comes handy in solving modulo operations for huge integers such as 24840081102364802172700 mod 97 :

SELECT (CAST(24840081102364802172700 AS DECIMAL(23,0))/97 - ROUND(CAST(24840081102364802172700 AS DECIMAL(23,0))/97 ,0,1)) *97 = 10.0

You can also round it altogether in the end.

You'd have to cast to decimal for smaller numbers

 select cast(power(cast(101 as float),50) as decimal(38,0)) % 221

or

 select power(cast(101 as decimal(38,0)),50) % 221

This fails though with such a large number

But then it makes no sense anyway for larger numbers.

• float is accurate to 15 signficant figures.
• 101 ^ 50 = 1.64463182184388E+100
• the margin of error (float approximation) is about 82 orders of magnitude (1E+82) higher than your modulo 221

Any answer from the modulo is utter rubbish

Edit:

Decimal goes to around 10^38

Take a float number at 10^39, or 1E+39, then you are accurate to around 1E24 (15 signficant figures).

Your modulo is 221 = 2.2E+2

You margin of error ie 1E+24/2.2E+2 = 4.4E+21

Just to be 100% clear, your accuracy is 4,400,000,000,000,000,000,000,000 times greater than your modulo.

It isn't even approximate: it's rubbish

If SQL Server supports it, the mod function should handle floats. But the computation you show, a float will not provide adequate precision. What happens when you leave off the cast?