# 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. – Philᵀᴹ Feb 21 '13 at 13:39
• Illustration and a possible solution: sqlfiddle.com/#!6/d41d8/2739 – dezso Feb 21 '13 at 13:51
• `floor` rounds down, so `2.9999999999` (which might be displayed as `3`) ends up as `2`. Why not use `round` instead? – Andomar Feb 21 '13 at 14:06
• @Andomar have you read the question? How would you calculate the number of digits of 999 with `round()`? – dezso Feb 21 '13 at 14:24
• @dezso just use ROUND instead of FLOOR, as Andomar suggested, and you should be all set – A-K Feb 22 '13 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 – Andomar Feb 21 '13 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. – Martin Smith Feb 21 '13 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? – Iain Samuel McLean Elder Feb 21 '13 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. – Martin Smith Feb 21 '13 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
``````