Remove duplicate values from comma separated variable in Oracle

Question

I have a variable (called: all_email_list) which contains 3 email address lists altogether. (I found some similar question but not the same with a proper solution)

Example: [email protected], [email protected],[email protected],[email protected], [email protected],[email protected] (it can contain spaces between comas but not all the time)

The desired output: [email protected], [email protected],[email protected],[email protected]

declare
first_email_list varchar2(4000);
second_email_list varchar2(4000);
third_email_list varchar2(4000);
all_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; 

    dbms_output.put_line(all_email_list);
end;

Any solution to solve this in a simple way? By regex maybe.

Koen Lostrie · Accepted Answer · 2021-09-15 12:58:40Z

6

Solution description. Use CTE to first split up the list of emails into rows with 1 email address per row (testd_rows). Then select distinct rows (testd_rows_unique) from testd_rows and finally put them back together with listagg. From 19c onwards you can use LISTAGG with the DISTINCT keyword.

set serveroutput on size 999999
clear screen
declare

  all_email_list varchar2(4000);
  l_unique_email_list varchar2(4000);
  

begin
  all_email_list := '[email protected], [email protected],[email protected],[email protected], [email protected],[email protected]';

  WITH testd_rows(email) AS
  (
  select regexp_substr (all_email_list, '[^, ]+', 1, rownum) split  
    from dual 
  connect by level <= length (regexp_replace (all_email_list, '[^, ]+'))  + 1
  ), testd_rows_unique(email) AS
  (
  SELECT distinct email FROM testd_rows
  )
  SELECT listagg(email, ',') WITHIN GROUP (ORDER BY email) 
    INTO l_unique_email_list 
    FROM testd_rows_unique;

  dbms_output.put_line(l_unique_email_list);
end;
/

[email protected],[email protected],[email protected],[email protected]

But ... why are you converting rows to a comma separated string and then de-duping it ? Use UNION to take out the duplicate values in a single SELECT statement and do LISTAGG on the values. No regexp needed then. UNION will skip duplicates as opposed to UNION ALL which returns all the rows.

DECLARE
  all_email_list varchar2(4000);
BEGIN
  WITH all_email (email) AS
  (
    select 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
    select email from table2 where CFT_ID =:P25_CFT_TEAM
    UNION
    select email from table3 WHERE :P25_ID = ID
  )
  SELECT listagg(email, ',') WITHIN GROUP (ORDER BY email) 
    INTO all_email_list 
    FROM all_email;

  dbms_output.put_line(all_email_list);
END;
/

edited Sep 15, 2021 at 12:58

answered Sep 13, 2021 at 13:51

Koen Lostrie

19.4k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AVrolet Over a year ago

Thanks for the answer! But in the second solution why you need the into third_email_list in the 10th line? @Koen Lostrie

Koen Lostrie Over a year ago

Ah - that is a typo. Fixed it ! Thanks for noticing

AVrolet Over a year ago

Thanks! Does it work if my 10th line is select address from table3 WHERE :P25_ID = ID instead of select email.... ? @Koen Lostrie

Koen Lostrie Over a year ago

it should as long as the datatypes are the same. Best way is to test it :)

Scott · Accepted Answer · 2021-09-14 01:50:16Z

You could leverage the apex_string.split table function to simplify the code.

12c+ makes it real clean

select listagg(distinct column_value,',') within group (order by null)
from apex_String.split(replace('[email protected], [email protected],[email protected],[email protected], [email protected],[email protected]'
                              ,' ')
                      ,',')

11g needs a wrapping table() and listagg doesn't support distinct.

select listagg(email,',') within group (order by null)
from
 (select distinct column_value email
  from table(apex_String.split(replace('[email protected], [email protected],[email protected],[email protected], [email protected],[email protected]',' '),','))
);

Collectives™ on Stack Overflow

Remove duplicate values from comma separated variable in Oracle

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related