oracle 去重函数（逗号分割后去重，行转列）

create or replace function aaa_distinct( a varchar2 )
return varchar2
as
results varchar2(2000):='';
begin

 

select listagg(CONTRACTNO_listagg ,',')
within group(order by CONTRACTNO_listagg) into results
from (
select distinct substr(a,
decode(level - 1, 0, 0, instr(a, ',', 1, level - 1)) + 1,
(decode(level,
regexp_count(a, ',') + 1,
length(a) + 1,
instr(a, ',', 1, level))) -
(decode(level - 1, 0, 0, instr(a, ',', 1, level - 1)) + 1)) as CONTRACTNO_listagg

from dual
connect by level <= regexp_count(a, ',') + 1

);

return results;
end;

分两次执行
select aaa_distinct(',111,222,333,111') from dual;