select col from(
select sys_connect_by_path(col,',')||',' col,level from(
select col,row_number() over(order by rownum) rn from (
select distinct substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
select ',1,2,5,9,1,2,5,9,1,3,9,' col from dual
) connect by rownum<length(translate(col,','||col,','))
)
)
connect by prior rn = rn -1 order by level desc
) where rownum=1
create or replace function non_redundant (acc_str in varchar2) return varchar2
is
v_str varchar2(30000) := replace(acc_str,',');
v_acc char(1);
type my_table is table of char(2) index by pls_integer;
v_mytable my_table;
v_string varchar2(1000);
begin
loop
exit when (v_str is null);
v_acc := substrb(v_str,1,1);
v_str := replace(v_str,v_acc);
v_mytable(to_number(v_acc)) := v_acc||',';
end loop;
for i in 1..10 loop
if v_mytable.EXISTS(i) then
v_string := v_string||v_mytable(i);
end if;
end loop;
return ','||v_string;
end;
/