create or replace function ZH_SPLIT(v_string in varchar2, --输入字符串
v_delimiter in varchar2) --输入分隔符
/*
去除一个字符串中重复的数据,例如:a,a,a,b,c,c输出a,b,c
*/
return varchar2 is
j int := 0;
i int := 1;
len_string int := 0;
len_delimiter int := 0;
str varchar2(4000);
v_return varchar2(4000);
begin
len_string := LENGTH(v_string);
len_delimiter := LENGTH(v_delimiter);
while j < len_string loop
j := INSTR(v_string, v_delimiter, i);
if j = 0 then
j := len_string;
str := SUBSTR(v_string, i);
if instr(v_return, str) > 0 then
null;
else
v_return := v_return || str || ',';
end if;
if i >= len_string then
exit;
end if;
else
str := SUBSTR(v_string, i, j - i);
i := j + len_delimiter;
if instr(v_return, str) > 0 then
null;
else
v_return := v_return || str || ',';
end if;
end if;
end loop;
v_return := substr(v_return, 1, length(v_return) - 1);
return v_return;
end;
------------------------------------------
select ZH_SPLIT('a,b,b,c,c,d',',') from dual
a,b,c,d
SQL> with tt as(
2 select 1 as id from dual union all
3 select 1 as id from dual union all
4 select 2 as id from dual union all
5 select 2 as id from dual union all
6 select 3 as id from dual union all
7 select 3 as id from dual union all
8 select 3 as id from dual)
9 select t.id from
10 (select id,row_number() over(partition by id order by 1) rn from tt) t
11 where t.rn=1;
ID
----------
1
2
3