CODEWITHSUNDEEP
hivehiveqlconcatenation
satish silveri
satish silveri

Reputation: 358

Concat String columns in hive

I need to concat 3 columns from my table say a,b,c. If the length of the columns is greater than 0 then I have to concat all 3 columns and store it as another column d in the below format.

1:a2:b3:c

I have tried the following query but I am not sure how to proceed as I am getting null as the result.

select a,b,c,
case when length(a) >0 then '1:'+a else '' end + case when length(b) > 0 then '2:'+b else '' end + case when length(c) > 0 then '3:'+c else '' end AS d
from xyz;

Appreciate the help :)

Upvotes: 2

Views: 8948

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Use concat() function: 

select a,b,c,
       concat(
              case when length(a)>0 then concat('1:',a) else '' end,
              case when length(b)>0 then concat('2:',b) else '' end,  
              case when length(c)>0 then concat('3:',c) else '' end 
             ) as d 
from (--test dataset
       select stack(4, 'a','b','c', --all
                      '','b','c',  --one empty
                      null,'b','c', --null
                      '','',''      --all empty
                   ) as (a,b,c) 
     )your_data;

Result:

OK
a       b       c       1:a2:b3:c
        b       c       2:b3:c
NULL    b       c       2:b3:c

Time taken: 0.284 seconds, Fetched: 4 row(s) - last one row is empty

As of Hive 2.2.0. you can use || operator instead of concat:

select a,b,c,
       case when length(a)>0 then '1:'||a else '' end||
       case when length(b)>0 then '2:'||b else '' end|| 
       case when length(c)>0 then '3:'||c else '' end as d

Upvotes: 5

Related Questions