CODEWITHSUNDEEP
sqlhadoophive
Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

How to trim leading zero in Hive

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"

Upvotes: 2

Views: 22457

Answers (3)

Vinod K
Vinod K

Reputation: 1

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>;

Upvotes: 0

Ronak Patel
Ronak Patel

Reputation: 3849

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>

Upvotes: 8

Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

nothing to do just cast the string in INT

SELECT CAST( "00000012300234"  AS INT);

it will return 12300234

Upvotes: 0

Related Questions