CODEWITHSUNDEEP
sqlhadoophiveclouderaimpala
fluent
fluent

Reputation: 65

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'.

Upvotes: 1

Views: 2995

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7407

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

Upvotes: 4

Related Questions