Counting the number of digits in column
33,876
Solution 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.
Solution 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
Author by
user2343837
Updated on June 01, 2020Comments
-
user2343837 almost 4 years
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?