Search code examples
sqlhiveimpala

Concat the values using the case condition

select
case when concat('ACC','-',NVL(trim(bvmo.booking),''),'-',NVL(trim(bvmo.org),''),'-',NVL(trim(bvma.sparm),''),'-',NVL(trim(bvmo.id),'')  like 'ACC--%' then
 NULL ELSE concatconcat('ACC','-',NVL(trim(bvmo.booking),''),'-',NVL(trim(bvmo.org),''),'-',NVL(trim(bvma.sparm),''),'-',NVL(trim(bvmo.id),'') END AS Parent_id
from bvmo

output:

 Parent-id
:----------:
ACC-1123-1344--
ACC-4567-6528--
ACC-7890-9827--
ACC-1143-8079--
ACC-1883-8944--

am expecting as the below output where i can remove the "-" in above case condition in which the values are not appearing in respective columns

Expected output:

 Parent-id
:----------:
ACC-1123-1344
ACC-4567-6528
ACC-7890-9827
ACC-1143-8079
ACC-1883-8944
Solution

  • Use concat_ws. It should take care of nulls in the way you wanted.

    select
    concat_ws('-', 'ACC', trim(bvmo.booking), trim(bvmo.org), trim(bvma.sparm), trim(bvmo.id)) AS Parent_id
from bvmo