Search code examples
sqlhadoophiveclouderaimpala

Convert scientific notation string to number in Hive

I have a dirty table on Cloudera with a number string column. Some numbers are as-is in their 8-digit form while others are in scientific notation e.g. 91234567 vs 9.1234567E7. When numbers end in zero(es), there are fewer decimals e.g. 9.12E7 for 91200000. How do I convert all of them to their 8-digit representation?

I have tried the following, to no avail:

-- Remove 'E7' then convert the string to a decimal
,CASE WHEN m_number LIKE '%E7' 
        THEN CAST(REPLACE(m_number, 'E7', '') AS DECIMAL(10,7)) * POW(10, 7)
        ELSE m_number END AS m_clean

Returns: AnalysisException: Incompatible return types 'DECIMAL(10,7)' and 'STRING' of exprs 'CAST(replace(m_number, 'E7', '') AS DECIMAL(10,7))' and 'm_number'.

Solution

  • How about simple cast ?

    cast (9.12e7 as  BIGINT)
OR
cast ('9.12E7' as  decimal(8,0))

    Pls check which one is working for you.

    Screenshot below. enter image description here