Is there an equivalent to concat_ws in oracle?

user_13253113 0
Viewed 3726

I have a ton of columns I am trying to aggregate together and most of them have NULL values. I want to separate values that do appear with a ';' but I cannot find an effective way to do this in oracle. CONCAT_WS would be exactly what I need as it would not add the delimeter between NULL values, but oracle does not support this. 

concat_ws(';','dx89','dx90','dx91','dx92') as diagnoses3

ORA-00904: "CONCAT_WS": invalid identifier

Using a function like this is similar but doesn't quite get me what I need as you can see the ';' on the end of the string since dx91 and dx92 are NULL values:

dx89||';'||dx90||';'||dx91||';'||dx92 as diagnoses2

I63.8;I63.9;;

Any help would be greatly appreciated!

concatenation oracle sql
1 Answers

You can use NVL2() function specific to Oracle DB together with pipe concatenation operators :

SELECT TRIM(LEADING ';' 
               FROM dx89||NVL2(dx90,';'||dx90,dx90)||
                          NVL2(dx91,';'||dx91,dx91)||
                          NVL2(dx92,';'||dx92,dx92)) AS "Concatenated String"
  FROM t

Demo

user_58413060
Related