CODEWITHSUNDEEP
sqloracle-databaseconcatenation
John
John

Reputation: 299

Concatenate multiple values and removing characaters when null

I have the below query

select id, CORD.LABEL ||' ('|| CORD.INTERVENT_TYPE || ')' ||' '||REFER.LABEL ||' ('|| REFER.INTERVENT_TYPE || ')'||' '|| INTER.LABEL ||' ('|| INTERVENT_TYPE || ')' AS INTERVEN_TYPE
from tableA

This is producing the below results. 

ID    INTERVEN_TYPE
1      () () Education(Other)

I would like to display results only when the values are not null. I would expect to see.

ID    INTERVEN_TYPE
1     Education(Other)

I've tried case expression, nvl and coalesce. Which one would work best as I cannot create the correct syntax?

Upvotes: 0

Views: 26

Answers (1)

forpas
forpas

Reputation: 164099

With CASE:

select 
id, 
TRIM(
CASE CORD.LABEL ||'('|| CORD.INTERVENT_TYPE || ')' 
    WHEN '()' THEN ''
    ELSE CORD.LABEL ||' ('|| CORD.INTERVENT_TYPE || ')' 
END 
||' '||
CASE REFER.LABEL ||'('|| REFER.INTERVENT_TYPE || ')'
    WHEN '()' THEN ''
    ELSE REFER.LABEL ||' ('|| REFER.INTERVENT_TYPE || ')'
END 
||' '|| 
CASE INTER.LABEL ||'('|| INTERVENT_TYPE || ')'
    WHEN '()' THEN ''
    ELSE INTER.LABEL ||' ('|| INTERVENT_TYPE || ')'
END 
)
AS INTERVEN_TYPE
from tableA

I removed now 1 space from the left parenthesis.
EDIT: Added TRIM function.
Maybe this would work too, if you eliminate () by REPLACE:

select 
id, TRIM(
REPLACE(CORD.LABEL ||' ('|| CORD.INTERVENT_TYPE || ')', '()')
||' '||
REPLACE(REFER.LABEL ||' ('|| REFER.INTERVENT_TYPE || ')', '()')
||' '|| 
REPLACE(INTER.LABEL ||' ('|| INTERVENT_TYPE || ')', '()')
) 
AS INTERVEN_TYPE
from tableA

Upvotes: 2

Related Questions