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:

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


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:

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


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?

  • 4
    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ᵀᴹ
    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?
    – Andomar
    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

2 Answers 2

  CAST(LOG(Number) / LOG(10) AS VARBINARY) AS LogOverLog
  VALUES (1000)
) AS Tally (Number);


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

  Number ,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
  ,LOG10(Number) - (LOG(Number) / LOG(10)) as Diff


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

Displaying the limit


  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.