CODEWITHSUNDEEP
sqloracle-databaseconcatenation
jonescm128
jonescm128

Reputation: 57

Is there an equivalent to concat_ws in oracle?

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!

Upvotes: 3

Views: 8524

Answers (2)

MT0
MT0

Reputation: 167832

You can use a CASE expression to concatenate only the non-NULL values with leading delimiters and then remove only the initial delimiter:

SELECT SUBSTR(
            CASE WHEN dx89 IS NOT NULL THEN ';'||dx89 END
         || CASE WHEN dx90 IS NOT NULL THEN ';'||dx90 END
         || CASE WHEN dx91 IS NOT NULL THEN ';'||dx91 END
         || CASE WHEN dx92 IS NOT NULL THEN ';'||dx92 END,
         2
       ) AS diagnoses2
FROM   table_name;

or

SELECT SUBSTR(
            NVL2(dx89, ';'||dx89, NULL)
         || NVL2(dx90, ';'||dx90, NULL)
         || NVL2(dx91, ';'||dx91, NULL)
         || NVL2(dx92, ';'||dx92, NULL),
         2
       ) AS diagnoses2
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name(dx89, dx90, dx91, dx92) AS
  SELECT 'A',  'B',  NULL, 'D'  FROM DUAL UNION ALL
  SELECT NULL, NULL, 'C',  NULL FROM DUAL UNION ALL
  SELECT 'A',  'B',  'C',  'D'  FROM DUAL UNION ALL
  SELECT ';;', NULL, NULL, NULL FROM DUAL

Both output:

DIAGNOSES2
A;B;D
C
A;B;C;D
;;

fiddle

If, instead of using SUBSTR, you use TRIM and the first non-NULL value has the delimiter character as the first character(s) in the string then you will remove too many characters and the final row of the sample data would output NULL. Instead, it is better to remove only the number of characters in the delimiter using SUBSTR.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 3

Related Questions