Search code examples
hiveconditional-statementscasecoalesce

coalesce two columns and one column has space

enter image description here

enter image description here

I have a table: customer and has three columns. I want to select the first not null column.

Select ID coalesce( columnA, ColumnB) from customer.

The Expect result should be : 101 AABB.

The actual result is 101 blank.

The columnA may have space, I trim the column.

Select ID coalesce( trim(columnA), ColumnB) from customer.

I get the same result: 101 blank.

How can I get result: 101 AABB? The ColumnA may have space, null value, and actual value

Solution

  • Use CONCAT. NULL is not same as space.COALESCE will return the first non null value and hence you are getting space. 

    select ID,CONCAT(trim(ColumnA),trim(ColumnB)) from customer

    EDIT

    select ID, 
CASE WHEN ColumnA is null OR trim(ColumnA)=' ' then ColumnB else ColumnA END 
from customer