Oracle 去掉重复字符串代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 Oracle 去掉重复字符串
作者:Dezai.CN / 发布于2012/3/13/ 999
<ol>
    <li><span><span class="keyword">create</span><span> </span><span class="op">or</span><span> </span><span class="func">replace</span><span> </span><span class="keyword">function</span><span> remove_rame_string(oldStr varchar2, sign varchar2)  </span></span></li>
    <li><span>  <span class="keyword">return</span><span> varchar2 </span><span class="keyword">is</span><span>  </span></span></li>
    <li><span>  </span></li>
    <li><span>  /****************************************************  </span></li>
    <li><span>  ** Oracle去掉重复字符串  </span></li>
    <li><span>  ** 函数名称：RemoveSameStr  </span></li>
    <li><span>  ** 参    数：【名称】         【类型 】      【说明】  </span></li>
    <li><span>  **           oldStr           varchar2       要处理的字符串  </span></li>
    <li><span>  **           sign             varchar2       字符串分隔符  </span></li>
    <li><span>  ** 返 回 值：Result           varchar2       不包含重复子串的记录  </span></li>
    <li><span>  ****************************************************/  </span></li>
    <li><span>  str          varchar2(2000);  </span></li>
    <li><span>  currentIndex number;  </span></li>
    <li><span>  startIndex   number;  </span></li>
    <li><span>  endIndex     number;  </span></li>
    <li><span>  </span></li>
    <li><span>  type str_type <span class="keyword">is</span><span> </span><span class="keyword">table</span><span> </span><span class="keyword">of</span><span> varchar2(30) </span><span class="keyword">index</span><span> </span><span class="keyword">by</span><span> binary_integer;  </span></span></li>
    <li><span>  arr str_type;  </span></li>
    <li><span>  Result varchar2(1000);  </span></li>
    <li><span><span class="keyword">begin</span><span>  </span></span></li>
    <li><span>  <span class="comment">-- 空字符串</span><span>  </span></span></li>
    <li><span>  if oldStr <span class="keyword">is</span><span> </span><span class="op">null</span><span> </span><span class="keyword">then</span><span>  </span></span></li>
    <li><span>    <span class="keyword">return</span><span>(</span><span class="string">''</span><span>);  </span></span></li>
    <li><span>  <span class="keyword">end</span><span> if;  </span></span></li>
    <li><span>  </span></li>
    <li><span>  <span class="comment">--字符串太长</span><span>  </span></span></li>
    <li><span>  if length(oldStr) > 2000 <span class="keyword">then</span><span>  </span></span></li>
    <li><span>    <span class="keyword">return</span><span>(oldStr);  </span></span></li>
    <li><span>  <span class="keyword">end</span><span> if;  </span></span></li>
    <li><span>  str := oldStr;  </span></li>
    <li><span>  </span></li>
    <li><span>  currentIndex := 0;  </span></li>
    <li><span>  startIndex   := 0;  </span></li>
    <li><span>  </span></li>
    <li><span>  loop  </span></li>
    <li><span>    currentIndex := currentIndex + 1;  </span></li>
    <li><span>    endIndex     := instr(str, sign, 1, currentIndex);  </span></li>
    <li><span>    if (endIndex <= 0) <span class="keyword">then</span><span>  </span></span></li>
    <li><span>      exit;  </span></li>
    <li><span>    <span class="keyword">end</span><span> if;  </span></span></li>
    <li><span>  </span></li>
    <li><span>    arr(currentIndex) := trim(substr(str,  </span></li>
    <li><span>                                     startIndex + 1,  </span></li>
    <li><span>                                     endIndex - startIndex - 1));  </span></li>
    <li><span>    startIndex := endIndex;  </span></li>
    <li><span>  <span class="keyword">end</span><span> loop;  </span></span></li>
    <li><span>  </span></li>
    <li><span>  <span class="comment">--取最后一个字符串:</span><span>  </span></span></li>
    <li><span>  arr(currentIndex) := substr(str, startIndex + 1, length(str));  </span></li>
    <li><span>  </span></li>
    <li><span>  <span class="comment">--去掉重复出现的字符串:</span><span>  </span></span></li>
    <li><span>  <span class="keyword">for</span><span> i </span><span class="op">in</span><span> 1 .. currentIndex - 1 loop  </span></span></li>
    <li><span>    <span class="keyword">for</span><span> j </span><span class="op">in</span><span> i + 1 .. currentIndex loop  </span></span></li>
    <li><span>      if arr(i) = arr(j) <span class="keyword">then</span><span>  </span></span></li>
    <li><span>        arr(j) := <span class="string">''</span><span>;  </span></span></li>
    <li><span>      <span class="keyword">end</span><span> if;  </span></span></li>
    <li><span>    <span class="keyword">end</span><span> loop;  </span></span></li>
    <li><span>  <span class="keyword">end</span><span> loop;  </span></span></li>
    <li><span>  </span></li>
    <li><span>  str := <span class="string">''</span><span>;  </span></span></li>
    <li><span>  <span class="keyword">for</span><span> i </span><span class="op">in</span><span> 1 .. currentIndex loop  </span></span></li>
    <li><span>    if arr(i) <span class="keyword">is</span><span> </span><span class="op">not</span><span> </span><span class="op">null</span><span> </span><span class="keyword">then</span><span>  </span></span></li>
    <li><span>      str := str || sign || arr(i);  </span></li>
    <li><span>      <span class="comment">--数组置空:</span><span>  </span></span></li>
    <li><span>      arr(i) := <span class="string">''</span><span>;  </span></span></li>
    <li><span>    <span class="keyword">end</span><span> if;  </span></span></li>
    <li><span>  <span class="keyword">end</span><span> loop;  </span></span></li>
    <li><span>  </span></li>
    <li><span>  <span class="comment">--去掉前面的标识符:</span><span>  </span></span></li>
    <li><span>  Result := substr(str, 2, length(str));  </span></li>
    <li><span>  </span></li>
    <li><span>  <span class="keyword">return</span><span>(Result);  </span></span></li>
    <li><span><span class="keyword">end</span><span> remove_rame_string; </span></span></li>
</ol>
试试其它关键字
　去掉重复字符串　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)