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?
It would be better to only get the
DISTINCT
items in the list and then you can use'|'
as the delimiter inLISTAGG
rather than','
: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 toabcdefghi
which is incorrect.Like this:
Which, for the sample data:
Outputs:
fiddle