Search code examples
bigdatadecimalroundingimpala

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

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