ORACLE 10G 怎么通过正则表达式去除重复值？
数据库：ORACLE10G

字符串（用/分隔的）： AB/AB/CD/CD/CD/CD/CD/EF/GHIJ/GHIJ/AB/CD/KLMN+OP/KLMN+OP/CD/AB

我想出来结果：AB/CD/EF/GHIJ/KLMN+OP

怎么用regexp_replace函数实现呢？


------解决方案--------------------
--regexp_replace估计无法实现,下面的方法可以:
DECLARE
STR_IN VARCHAR2(2000) DEFAULT 'AB/AB/CD/CD/CD/CD/CD/EF/GHIJ/GHIJ/AB/CD/KLMN+OP/KLMN+OP/CD/AB';
V_STR_TEMP VARCHAR2(2000);
V_SPLIT VARCHAR2(1) := '/';
V_OUT VARCHAR2(2000) := '';
BEGIN
V_STR_TEMP := V_SPLIT || STR_IN || V_SPLIT;
FOR I IN (SELECT DISTINCT SUBSTR(V_STR_TEMP,
INSTR(V_STR_TEMP, V_SPLIT, 1, ROWNUM) + 1,
INSTR(V_STR_TEMP, V_SPLIT, 1, ROWNUM + 1) -
INSTR(V_STR_TEMP, V_SPLIT, 1, ROWNUM) - 1) BB
FROM (SELECT NULL FROM USER_USERS WHERE ROWNUM = 1)
CONNECT BY ROWNUM <=
LENGTH(STR_IN) - LENGTH(REPLACE(STR_IN, V_SPLIT, '')) + 1
ORDER BY 1) LOOP
IF LENGTH(V_OUT) > 0 THEN
V_OUT := V_OUT || V_SPLIT || I.BB;
ELSE
V_OUT := I.BB;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_OUT);
END;
/

--输出:
AB/CD/EF/GHIJ/KLMN+OP 

引用楼主 it_1980 的帖子:

数据库：ORACLE10G

字符串（用/分隔的）： AB/AB/CD/CD/CD/CD/CD/EF/GHIJ/GHIJ/AB/CD/KLMN+OP/KLMN+OP/CD/AB

我想出来结果：AB/CD/EF/GHIJ/KLMN+OP

怎么用regexp_replace函数实现呢？

------解决方案--------------------
用自定义函数，分离后，再合并

------解决方案--------------------

SQL code

SELECT ltrim(REPLACE(wmsys.wm_concat(DISTINCT 
                  regexp_substr('/AB/AB/CD/CD/CD/CD/CD/EF/GHIJ/GHIJ/AB/CD/KLMN+OP/KLMN+OP/CD/AB',
                                '/[^\/]+',1,ROWNUM)),
               ',/','/'),'/')
FROM   dual
CONNECT BY instr('/AB/AB/CD/CD/CD/CD/CD/EF/GHIJ/GHIJ/AB/CD/KLMN+OP/KLMN+OP/CD/AB','/',1,ROWNUM) > 0;

LTRIM(REPLACE(WMSYS.WM_CONCAT(
--------------------------------------------
AB/CD/EF/GHIJ/KLMN+OP