How to replace comma with pipe for LISTAGG

Question

I have a LISTAGG created with a REGEXP_REPLACE. Now I want to replace the comma with pipe.

Query:

SELECT
ID,
REGEXP_REPLACE(LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name ), '([^,]+)(,\1)(,|$)', '\1|\3')  
From my_table;

Output:

|ID|USER_NAME   |
|1 |A1|,B12|,C32|
|2 |A1          |
|3 |B12,C32     |

what I want to see is:

|ID|USER_NAME |
|1 |A1|B12|C32|
|2 |A1        |
|3 |B12|C32   |

Where should the comma be removed.
How is the code look like?

Answer 1

It would be better to only get the DISTINCT items in the list and then you can use '|' as the delimiter in LISTAGG rather than ',':

SELECT ID,
       LISTAGG(DISTINCT user_name, '|') WITHIN GROUP (ORDER BY user_name)
         AS user_names
FROM   my_table
GROUP BY id;

If you cannot use DISTINCT then you need to match the entire term which you can do by prepending and appending a delimiter to each term and then aggregating and then matching repeating terms including the surrounding delimiters and removing duplicates; finally you need to remove the leading and trailing delimiters and the double delimiters between terms.

Note: If you naively match partial terms then you will find that abc|abcdef|defghi gets transformed to abcdefghi which is incorrect.

Like this:

SELECT ID,
       REPLACE(
         TRIM(
           BOTH '|' FROM
           REGEXP_REPLACE(
             LISTAGG('|' || user_name || '|', NULL) WITHIN GROUP (ORDER BY user_name),
             '(\|[^|]+\|)(\1)+', '\1'
           )
         ),
         '||',
         '|'
       ) AS user_names
FROM   my_table
GROUP BY id;

Which, for the sample data:

CREATE TABLE my_table (id, user_name) AS
SELECT 1, 'abc' FROM DUAL UNION ALL
SELECT 1, 'abc' FROM DUAL UNION ALL
SELECT 1, 'abcdef' FROM DUAL UNION ALL
SELECT 1, 'def' FROM DUAL UNION ALL
SELECT 1, 'def' FROM DUAL UNION ALL
SELECT 1, 'defghi' FROM DUAL;

Outputs:

ID	USER_NAMES
1	abc|abcdef|def|defghi

fiddle