oracle如何去除字符串中的重复字符 

 1 create or replace function remove_rame_string(oldStr varchar2, sign varchar2)  
 2   return varchar2 is  
 3    
 4   /****************************************************  
 5   ** Oracle去掉重复字符串  
 6   ** 函数名称：RemoveSameStr  
 7   ** 参    数：【名称】         【类型 】      【说明】  
 8   **           oldStr           varchar2       要处理的字符串  
 9   **           sign             varchar2       字符串分隔符  
10   ** 返 回 值：Result           varchar2       不包含重复子串的记录  
11   ****************************************************/  
12   str          varchar2(2000);  
13   currentIndex number;  
14   startIndex   number;  
15   endIndex     number;  
16    
17   type str_type is table of varchar2(30) index by binary_integer;  
18   arr str_type;  
19   Result varchar2(1000);  
20 begin  
21   -- 空字符串  
22   if oldStr is null then  
23     return('');  
24   end if;  
25    
26   --字符串太长  
27   if length(oldStr) > 2000 then  
28     return(oldStr);  
29   end if;  
30   str := oldStr;  
31    
32   currentIndex := 0;  
33   startIndex   := 0;  
34    
35   loop  
36     currentIndex := currentIndex + 1;  
37     endIndex     := instr(str, sign, 1, currentIndex);  
38     if (endIndex <= 0) then  
39       exit;  
40     end if;  
41    
42     arr(currentIndex) := trim(substr(str,  
43                                      startIndex + 1,  
44                                      endIndex - startIndex - 1));  
45     startIndex := endIndex;  
46   end loop;  
47    
48   --取最后一个字符串:  
49   arr(currentIndex) := substr(str, startIndex + 1, length(str));  
50    
51   --去掉重复出现的字符串:  
52   for i in 1 .. currentIndex - 1 loop  
53     for j in i + 1 .. currentIndex loop  
54       if arr(i) = arr(j) then  
55         arr(j) := '';  
56       end if;  
57     end loop;  
58   end loop;  
59    
60   str := '';  
61   for i in 1 .. currentIndex loop  
62     if arr(i) is not null then  
63       str := str || sign || arr(i);  
64       --数组置空:  
65       arr(i) := '';  
66     end if;  
67   end loop;  
68    
69   --去掉前面的标识符:  
70   Result := substr(str, 2, length(str));  
71    
72   return(Result);  
73 end remove_rame_string;
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);
  currentIndex number;
  startIndex   number;
  endIndex     number;

  type str_type is table of varchar2(30) index by binary_integer;
  arr str_type;
  Result varchar2(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;
  end loop;

  --取最后一个字符串:
  arr(currentIndex) := substr(str, startIndex + 1, length(str));

  --去掉重复出现的字符串:
  for i in 1 .. currentIndex - 1 loop
    for j in i + 1 .. currentIndex loop
      if arr(i) = arr(j) then
        arr(j) := '';
      end if;
    end loop;
  end loop;

  str := '';
  for i in 1 .. currentIndex loop
    if arr(i) is not null then
      str := str || sign || arr(i);
      --数组置空:
      arr(i) := '';
    end if;
  end loop;

  --去掉前面的标识符:
  Result := substr(str, 2, length(str));

  return('[' || Result || ']');
end remove_rame_string;

UPDATE TEST_CLOB set "STR" = remove_rame_string(STR, ',', '"str1","str2", "str2", "str3"') WHERE ID='1';

 https://zhidao.baidu.com/question/425459266020298772.html

posted on 2018-01-11 14:31  lpx15312  阅读(2932)  评论(0)  编辑  收藏  举报

导航