如何将LISTAGG中的逗号替换为竖线？修正现有正则查询问题

需要移除的逗号 #

所有用于分隔不同用户名的逗号，包括：

• 未被替换的逗号（如ID3结果B12,C32中的,）；
• 替换后残留的逗号（如ID1结果A1|,B12中的,）。

问题根源 #

你原SQL里的正则表达式'([^,]+)(,\1)(,|$)'仅针对重复的用户名做替换（比如把A1,A1改成A1），但没处理所有逗号分隔符，反而在替换时错误保留了部分逗号；同时原SQL缺少GROUP BY ID，会导致语法错误。

修正后的SQL #

分两种场景给出方案：

场景1：不需要去重，仅替换分隔符

直接在LISTAGG中用竖线作为分隔符，无需额外正则，简单高效：

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

场景2：需要去重（同一ID下重复用户名只保留一个）

优先用LISTAGG(DISTINCT)（多数主流数据库支持，如Oracle 12c+、PostgreSQL 16+），性能比正则替换更好：

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

如果你的数据库不支持LISTAGG(DISTINCT)，可以用正则先去重再替换逗号：

SELECT
  ID,
  REPLACE(
    REGEXP_REPLACE(
      LISTAGG(user_name, ',') WITHIN GROUP (ORDER BY user_name),
      '([^,]+)(,\1)+', '\1' -- 先移除重复的用户名
    ), ',', '|' -- 再把所有逗号换成竖线
  ) AS USER_NAME
FROM my_table
GROUP BY ID;

内容的提问来源于stack exchange，提问作者BiSaM