How to trim leading zero in Hive

Question

How to trim leading zero in Hive, I search too much on google but I didn't get any correct thing which is useful for my problem.

If digit is "00000012300234" want result like "12300234"

Answer 1

you can achieve it by using: regexp_replace String Function

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

The following removes leading zeroes, but leaves one if necessary (i.e. it wouldn't just turn "0" to a blank string).

hive> SELECT regexp_replace( "00000012300234","^0+(?!$)","") ;
OK
12300234
Time taken: 0.156 seconds, Fetched: 1 row(s)

hive> SELECT regexp_replace( "000000","^0+(?!$)","") ;
OK
0
Time taken: 0.157 seconds, Fetched: 1 row(s)

hive> SELECT regexp_replace( "0","^0+(?!$)","") ;
OK
0
Time taken: 0.12 seconds, Fetched: 1 row(s)

OR Using CAST - cast to int to string:

hive> SELECT CAST(CAST( "00000012300234"  AS INT) as string);
OK
12300234
Time taken: 0.115 seconds, Fetched: 1 row(s)

hive> SELECT CAST( "00000012300234"  AS INT);
OK
12300234
Time taken: 0.379 seconds, Fetched: 1 row(s)
hive> 

Answer 2

SELECT CAST( "00000012300234" AS INT) FROM <your_table> ;

--above SQL works works. But in case the number goes above INT range, then you need to have "BIGNINT" instead of "INT". Else you will see NULLs :-)

SELECT CAST( "00000012300234" AS BIGINT) FROM <your_table>;