concat and cast in HIVE

user_16076318 0
Viewed 252

I have number of records that include couple of columns with leading zeros. I would like to remove leading zeros and combined 3 columns into one with hyphen. I am using HIVE.

Column1    column2    column3
0123        12342567    001


desired output
123-12342567-1
hiveql hive concatenation sql
1 Answers

To remove leading zeroes you can use regexp_replace(col, '^0+', ''), then concatenate using concat_ws():

with mytable as ( select
'0123' Column1, '12342567' column2, '001' column3 )

select concat_ws('-',
                 regexp_replace(Column1,'^0+',''),
                 regexp_replace(Column2,'^0+',''),
                 regexp_replace(Column3,'^0+','')

                )
  from mytable;

Result:

123-12342567-1

In case some column value is NULL, concat_ws will skip it along with separator. For example if column2 is NULL, the result will be 123-1

If you want to transform NULL values to something else, use NVL(col, 'value if NULL') function, for example regexp_replace(NVL(column2, ''),'^0+','') will return empty string instead of NULL and concat_ws will return 123--1

user_27003440
Related