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: test@asd.com, test2@asd.com,test@asd.com,test3@asd.com, test4@asd.com,test2@asd.com
(it can contain spaces between comas but not all the time)
The desired output: test@asd.com, test2@asd.com,test3@asd.com,test4@asd.com
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.
Advertisement
Answer
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 := 'test@asd.com, test2@asd.com,test@asd.com,test3@asd.com, test4@asd.com,test2@asd.com'; 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; / test2@asd.com,test3@asd.com,test4@asd.com,test@asd.com
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; /