Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 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

2 Answers 2

up vote 6 down vote accepted
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)

share|improve this answer
    
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 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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