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;