# How can FLOOR(3) equal 2?

I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer.

Mathematically, the number of decimal digits in an integer `n` is `1 + floor(log(n))`, where log is the common logarithm (base 10).

There are several ways to construct an equivalent expression using built-in functions, but some of them give incorrect results. Can someone explain why?

Here is an example.

### How to calculate the log?

The simplest way to calculate the common logarithm is to use the `LOG10` function.

If you prefer one function for all logarithms you can use the `LOG` function and specify base 10 with the second parameter.

Prior to 2012, SQL Server's `LOG` function would calculate only the natural log (base e=2.71828...). You can calculate the log to an arbitrary base of a number by dividing the natural logarithm of the number by the natural logarithm of the base.

The following query calculates all three expressions for some example values:

``````SELECT
Number,
LOG(Number, 10) AS LogAB,
LOG10(Number) AS LogTen,
LOG(Number) / LOG(10) AS LogOverLog
FROM (
VALUES (999), (1000), (1001)
) AS Tally (Number);
``````

Output:

``````Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932
``````

I have chosen the values 999, 1000, and 1001 because 1000 is a point where the number of digits steps up. 999 has 3 digits, 1000 has 4.

The value of all three expressions is visibly the same, and looks correct.

Let's move on to the floor step.

### How to calculate the floor?

You can take the floor of each log in the previous example using a query like this:

``````SELECT
Number,
FLOOR(LOG(Number, 10)) AS FloorLogAB,
FLOOR(LOG10(Number)) AS FloorLogTen,
FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
VALUES (999), (1000), (1001)
) AS Tally (Number);
``````

Output:

``````Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3
``````

The values of each expression for 999 and 1001 are equal and correct. If we added 1 to each value, we would have a count of 3 digits in 999 and a count of 4 digits in 1001.

The values for 1000 are not the same! If we added 1 to each value, we would have a count of 4 digits in 1000 if we used the `LOG10` function, and a count of 3 digits if we used the `LOG` function in either form.

There is an inconsistency here!

### How can FLOOR(3) equal 2?

The implication is clear: using the `LOG` function would give me an incorrect count for some values, so I should use the `LOG10` function.

But the value of each log expression itself is identical and correct. Why does the floor function produce different values from their input?

• Ignoring your `floor()` question, why don't you just use `select len( cast( 999 as varchar));` (or similar) to do this? I must be missing something. Commented Feb 21, 2013 at 13:39
• Illustration and a possible solution: sqlfiddle.com/#!6/d41d8/2739 Commented Feb 21, 2013 at 13:51
• `floor` rounds down, so `2.9999999999` (which might be displayed as `3`) ends up as `2`. Why not use `round` instead? Commented Feb 21, 2013 at 14:06
• @Andomar have you read the question? How would you calculate the number of digits of 999 with `round()`? Commented Feb 21, 2013 at 14:24
• @dezso just use ROUND instead of FLOOR, as Andomar suggested, and you should be all set
– A-K
Commented Feb 22, 2013 at 14:22

``````SELECT
Number,
CAST(LOG(Number, 10) AS VARBINARY) AS LogAB,
CAST(LOG10(Number) AS VARBINARY) AS LogTen,
CAST(LOG(Number) / LOG(10) AS VARBINARY) AS LogOverLog
FROM (
VALUES (1000)
) AS Tally (Number);
``````

Returns

``````Number      LogAB                   LogTen                  LogOverLog
----------- ----------------------- ----------------------- ----------------------
1000        0x4007FFFFFFFFFFFF      0x4008000000000000      0x4007FFFFFFFFFFFF
``````

`0x4008000000000000` is exactly 3.

`0x4007FFFFFFFFFFFF` is 2.99999999999999955591079014994.

If you are looking for an efficient expression maybe a `CASE` expression with the 10 different cases would actually work out less CPU intensive than calculating logarithms (or possibly you could have nested case expressions to do a trinary search)

• For efficiency, I don't think anything beats `len(cast(999 as varchar))` from Phil's comment Commented Feb 21, 2013 at 16:24
• @Andomar Not sure about that. A few comparison operations against the numeric value may work out better than constructing a new string but talking about micro optimisations that are highly unlikely to make any meaningful difference. Commented Feb 21, 2013 at 16:29
• Thanks for the explanation. The confusion was caused by SSMS rounding the values! Is there any way to control whether SSMS rounds numbers like that? Commented Feb 21, 2013 at 17:12
• @IainElder - I don't think so. I don't see anything in the options for that. You could also amend the above to cast to `DECIMAL(38,37)` rather than `VARBINARY` to see greater precision. Commented Feb 21, 2013 at 17:26

I would call it a rounding problem ...

``````declare @a table(Number int)
insert into @a Values (999),(1000),(1001);

SELECT
Number ,
LOG10(Number) AS LogTen,
LOG(Number) / LOG(10) AS LogOverLog
,LOG10(Number) - (LOG(Number) / LOG(10)) as Diff
FROM @a
``````

Output

``````999 2,99956548822598    2,99956548822598    0
1000    3   3   4,44089209850063E-16
1001    3,00043407747932    3,00043407747932    4,44089209850063E-16
``````

Displaying the limit

``````SELECT
Number,

FLOOR(LOG10(Number)) AS FloorLogTen,
FLOOR(LOG(Number) / LOG(10) ) AS FloorLogOverLog,
FLOOR(LOG(Number) / LOG(10) + 2.22044E-16) AS LessCorrection,
FLOOR(LOG(Number) / LOG(10) + 2.22045E-16) AS Overcorrection
FROM @a
``````