converting varchar(7) to decimal (7,5) in hive

Question

I have a values like in hive table

0050000
0100000
0005000 

I would like to convert this to decimal(7,5) I tried

SELECT CAST(column_A AS DECIMAL(7,5)) AS converted_column
FROM table_name;

But showing result as NULL can anyone please suggest the solution for this.

expecting result should be 0.05000

Answer 1

In Hive, the TRIM() function should work for trimming leading and trailing spaces. However, if you're encountering issues with the TRIM() function, you can alternatively use REGEXP_REPLACE() to remove any leading or trailing spaces.

Here's how you can modify the query using REGEXP_REPLACE():

SELECT 
CAST(REGEXP_REPLACE(column_A, '^\\s+|\\s+$', '') AS DECIMAL(7,5)) 
AS converted_column
FROM table_name;

This SQL query will trim leading zeros from the values in column_A and then convert them to decimal(7,5). This should give you the desired result without NULL values.