目前有如下2种方式
1.有特定关联规则的,以字符串维度去重
create or replace function fun_rep_repeat_str(p_str varchar2)
return varchar2 deterministic is
type v_type is table of varchar2(4000);
v_tab v_type;
v_res varchar2(4000);
cursor c_get_str is
select regexp_substr(p_str, '[^;]+', 1, level) str
from dual
connect by level <= length(regexp_replace(p_str, '[^;]')) + 1;
begin
open c_get_str;
fetch c_get_str bulk collect into v_tab;
close c_get_str;
--集合去重
v_tab:=set(v_tab);
for x in 1.. v_tab.count loop
v_res:=v_res||v_tab(x)||';';
end loop;
return rtrim(v_res,';');
end;
select fun_rep_repeat_str('数学;乒乓球;篮球;数学;数学;体育;乒乓球') from dual;
2.如果是去掉重复单个子符,上面的也可以用
select regexp_replace('a;b;v;a',';a','',2) from dual;
或者:
create or replace function fun_repeat_char(p_str varchar2)
return varchar2 deterministic is
v_res varchar2(4000);
v_count pls_integer;
v_temp varchar2(4000);
begin
if p_str is null or length(p_str) = 1 then
return p_str;
end if;
v_count := length(p_str) - 1;
for x in 1 .. v_count loop
v_temp := substr(p_str, x, 1);
if nvl(instr(v_res, v_temp, 1, 1),0)<= 0 then
v_res := v_res || v_temp;
end if;
end loop;
return v_res;
end;
/
select fun_repeat_char('AAAAABBDHDKAJHDKASLKASJKLASLAAA') from dual;