create or replace function remove_rame_string(oldStr varchar2, sign varchar2, newStr varchar2)return varchar2 is
/****************************************************
** Oracle去掉重复字符串
** 函数名称:RemoveSameStr
** 参 数:【名称】 【类型 】 【说明】
** oldStr varchar2 要处理的字符串
** sign varchar2 字符串分隔符
** newStr varchar2 新添加的字符串,以逗号隔开
** 返 回 值:Result varchar2 不包含重复子串的记录
****************************************************/
str varchar2(2000);
currentIndexnumber;
startIndexnumber;
endIndexnumber;
type str_typeis table of varchar2(30) index bybinary_integer;
arr str_type;
Resultvarchar2(1000);begin
--空字符串
if oldStr is null then
return('[' || newStr || ']');end if;--字符串太长
if length(oldStr) > 2000 then
return(oldStr);end if;str :=oldStr;--拼接字符串,在"]"之前添加新创建的字符串
str := substr(str, 0, instr(str, ']')-1) || ','||newStr;str := substr(str, instr(str, '[')+1, length(str));
currentIndex := 0;
startIndex := 0;
loop
currentIndex := currentIndex + 1;
endIndex := instr(str, sign, 1, currentIndex);if (endIndex <= 0) then
exit;end if;
arr(currentIndex) := trim(substr(str,
startIndex+ 1,
endIndex- startIndex - 1));
startIndex :=endIndex;endloop;--取最后一个字符串:
arr(currentIndex) := substr(str, startIndex + 1, length(str));--去掉重复出现的字符串:
for i in 1 .. currentIndex - 1loopfor j in i + 1.. currentIndex loopif arr(i) = arr(j) thenarr(j) := '';end if;endloop;endloop;str := '';for i in 1.. currentIndex loopif arr(i) is not null then
str := str || sign ||arr(i);--数组置空:
arr(i) := '';end if;endloop;--去掉前面的标识符:
Result := substr(str, 2, length(str));return('[' || Result || ']');endremove_rame_string;UPDATE TEST_CLOB set "STR" = remove_rame_string(STR, ',', '"str1","str2", "str2", "str3"') WHERE ID='1';