Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

enter image description here

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?

like image 684
user2343837 Avatar asked Jul 29 '14 02:07

user2343837


People also ask

How do you count the number of digits in a column?

select len(cast(code as int)), code from tbl1 where code is not null; Presumably, some sort of decimal values are getting counted.

How do I count the number of digits in SQL?

SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

How do you count the number of digits in a number?

The formula will be integer of (log10(number) + 1). For an example, if the number is 1245, then it is above 1000, and below 10000, so the log value will be in range 3 < log10(1245) < 4. Now taking the integer, it will be 3. Then add 1 with it to get number of digits.


2 Answers

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.

like image 145
Gordon Linoff Avatar answered Nov 03 '22 01:11

Gordon Linoff


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
like image 34
Glen Avatar answered Nov 03 '22 00:11

Glen