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

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)

StackExchange Database Administrators Q#35159, answer score: 6