-1

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

select power(cast(101 as float),50)%221
gbn
  • 422,506
  • 82
  • 585
  • 676
yanzi
  • 31
  • 1
  • 1
  • 3
  • 1
    -1 for being mathematically challenged, comparing SQL Server to Windows Calculator. – gbn Dec 28 '10 at 12:11

3 Answers3

2

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.

sromcevic
  • 71
  • 1
  • 6
1

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • It was just an example. I can have any number as dividend or divisor. So, I use float. Is there any way to apply % to float numbers? – yanzi Dec 28 '10 at 09:07
  • Unfortunately, I can't. my numbers can be bigger than the limits of decimal – yanzi Dec 28 '10 at 09:55
  • @kkab: in which case, you're back to my comments about margin of error. If you exceed 1E38 (limit of decimaL) you have to go to float. 221 is around 2E2. At 1E39, say, with 15 sig figures (1E15) you have an error marging of 5E21 between your number accuracy (1E39-1E15 = 1E24) and 2E2. That is an error margin 5,000,000,000,000,000,000,000,000 greater then your divisor of 221. So your modulo has **no** meaning. – gbn Dec 28 '10 at 11:23
  • then, how do u assume how windows calculator calculates? There I get result: power(101,50)%221=35 – yanzi Dec 28 '10 at 12:01
  • 1
    @kkab: "windows calculator" is *not* "SQL Server". It won't use "float". I'm sure google tells you how it's done somewhere. – gbn Dec 28 '10 at 12:10
0

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?

ysth
  • 96,171
  • 6
  • 121
  • 214