Counting the number of digits in column

问题:

Here is my code

select len(cast(code as float)),code 
from tbl1
where code is not null

and this is the output:

I want a count of digits in the code column. I don't understand why the last one is counted as 12 and not 8?

回答1:

Cast it as an int instead:

select len(cast(code as int)), code 
from tbl1
where code is not null;

Presumably, some sort of decimal values are getting counted.

回答2:

Get the number's power of 10 and add 1. This works either if ints or reals to count the number of digits of the whole number part (note using LOG10 only works on positive numbers so I have applied ABS to get around this issue, may not be required for your data):

SELECT code, CASE WHEN Number = 0 THEN 1
                  ELSE FLOOR(LOG10(ABS(code))) + 1 AS NDigits
FROM tbl1