[FIXED] How to find which data have the longest decimal places in impala

Issue

I have a column stored in decimal(28, 7). How do I know which data have the longest decimal places in impala?

For example:
0.0000001 -> 7 decimal places
0.0100000 -> 2 decimal places
0.1000000 -> 1 decimal places

Solution

use below SQL

length(substr( Rtrim(cast(0.0100000  as string),'0'),instr( Rtrim(cast(0.0100000  as string),'0'),'.')+1)
) len

rtrim - is used to remove trailing 0s from the data.
instr - is used to locate position of '.'.
substr - is used to cut strings after position of '.'.

sample SQL-

SELECT length(substr( Rtrim(cast(0.0100000  as string),'0'),instr( Rtrim(cast(0.0100000  as string),'0'),'.')+1)
) len

Answered By - Koushik Roy

Answer Checked By - Cary Denson (PHPFixing Admin)