PL SQL中如何去掉字符串中重复的字符

Myth丶恋晨 2022-05-29 07:30 230阅读 0赞

CREATE OR REPLACE FUNCTION remove_rame_string(oldstr VARCHAR2,
                                                  p_sign VARCHAR2) RETURN VARCHAR2 IS
      /**************************************************** 
      ** Oracle去掉重复字符串 
      ** 函数名称：RemoveSameStr 
      ** 参    数：【名称】         【类型 】      【说明】 
      **           oldStr           varchar2       要处理的字符串 
      **           sign             varchar2       字符串分隔符 
      ** 返 回 值：l_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;
      l_result VARCHAR2(1000);
    BEGIN
      -- 空字符串 
      IF oldstr IS NULL THEN
        RETURN('');
      END IF;
      --字符串太长 
      IF length(oldstr) > 2000 THEN
        RETURN(oldstr);
      END IF;
      str          := oldstr;
      currentindex := 0;
      startindex   := 0;
      LOOP
        currentindex := currentindex + 1;
        endindex     := instr(str, p_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) AND arr(i) = p_sign 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 || p_sign || arr(i);
          --数组置空: 
          arr(i) := '';
        END IF;
      END LOOP;
      --去掉前面的标识符: 
      l_result := substr(str, 2, length(str));
      RETURN(l_result);
    END remove_rame_string;