Oracle中去除逗号分隔变量all_email_list中重复值的方法

有两种常用的实现方式，第一种是从数据源层面直接去重聚合，逻辑更简洁不易出错，第二种是你提到的对拼接后的字符串通过正则拆分再去重处理：

方案1：数据源直接去重（推荐） #

不需要先分别拼接再处理，直接把三个来源的邮箱合并去重后再聚合，代码如下：

declare
    all_email_list varchar2(4000);
begin
    select listagg(EMAIL, ',') within group (order by EMAIL)
    into all_email_list
    from (
        -- 合并三个来源的邮箱后去重
        select distinct EMAIL from UM_USER a left join UM_USERROLLE b on (a.mynetuser=b.NT_NAME) left join UM_RULES c on (c.id=b.RULEID) where RULEID = 902
        union all
        select distinct EMAIL from table2 where CFT_ID =:P25_CFT_TEAM
        union all
        select EMAIL from table3 WHERE :P25_ID = ID
    ) t;
    dbms_output.put_line(all_email_list);
end;

注：如果单个来源内部已经保证无重复邮箱，可以去掉子查询里的distinct，直接在外层统一去重即可

方案2：对已拼接的all_email_list用正则处理 #

如果一定要处理已经拼接好的带空格、重复的邮箱字符串，可以用正则拆分、去重后再聚合，代码如下：

declare
first_email_list varchar2(4000);
second_email_list varchar2(4000);
third_email_list varchar2(4000);
all_email_list varchar2(4000);
dedup_email_list varchar2(4000);
begin
    select listagg(EMAIL,',') into first_email_list from UM_USER a left join UM_USERROLLE b on (a.mynetuser=b.NT_NAME) left join UM_RULES c on (c.id=b.RULEID) where RULEID = 902;
    select listagg(EMAIL,',') into second_email_list from table2 where CFT_ID =:P25_CFT_TEAM; 
    select EMAIL into third_email_list from table3 WHERE :P25_ID = ID;

    all_email_list:= first_email_list || ',' || second_email_list || ',' || third_email_list; 

    -- 正则拆分去重后重新拼接
    select listagg(trim(email), ',') into dedup_email_list
    from (
        select distinct regexp_substr(all_email_list, '[^,]+', 1, level) as email
        from dual
        connect by regexp_substr(all_email_list, '[^,]+', 1, level) is not null
    ) t;

    dbms_output.put_line(dedup_email_list);
end;

正则逻辑说明： #

• regexp_substr(all_email_list, '[^,]+', 1, level) 按逗号拆分字符串，逐行返回每个邮箱片段
• trim(email) 去除邮箱两端的多余空格
• distinct 完成去重逻辑，最后用listagg重新拼接为逗号分隔的字符串

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