如何在Oracle中通过regexp_replace从逗号分隔的列表中删除重复项? [英] How to remove duplicates from comma separated list by regexp_replace in Oracle?
本文介绍了如何在Oracle中通过regexp_replace从逗号分隔的列表中删除重复项?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有
POW,POW,POWPRO,PRO,PRO,PROUTL,TNEUTL,TNEUTL,UTL,UTLTNE,UTL,UTLTNE
我想要
POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
我尝试了
select regexp_replace('POW,POW,POWPRO,PRO,PRO,PROUTL,TNEUTL,TNEUTL,UTL,UTLTNE,UTL,UTLTNE','([^,]+)(,\1)+','\1') from dual
然后我得到输出
POWPROUTL,TNEUTL,UTLTNE,UTLTNE
但是我希望输出是
POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
请帮助.
推荐答案
两个仅使用SQL的解决方案和第三个使用小型/简单PL/SQL函数的解决方案,这使得最终SQL查询非常短.
Two solutions that use only SQL and a third solution that uses a small/simple PL/SQL function which makes for a very short final SQL query.
Oracle设置:
CREATE TABLE data ( value ) AS
SELECT 'POW,POW,POWPRO,PRO,PRO,PROUTL,TNEUTL,TNEUTL,UTL,UTLTNE,UTL,UTLTNE' FROM DUAL;
CREATE TYPE stringlist AS TABLE OF VARCHAR2(4000);
/
查询1 :
SELECT LISTAGG( t.COLUMN_VALUE, ',' ) WITHIN GROUP ( ORDER BY t.COLUMN_VALUE ) AS list
FROM data d,
TABLE(
SET(
CAST(
MULTISET(
SELECT REGEXP_SUBSTR( d.value, '[^,]+', 1, LEVEL )
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( d.value, '[^,]+' )
) AS stringlist
)
)
) t
GROUP BY d.value;
输出:
LIST
---------------------------------------
POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
查询2 :
SELECT ( SELECT LISTAGG( COLUMN_VALUE, ',' ) WITHIN GROUP ( ORDER BY ROWNUM )
FROM TABLE( d.uniques ) ) AS list
FROM (
SELECT ( SELECT CAST(
COLLECT(
DISTINCT
REGEXP_SUBSTR( d.value, '[^,]+', 1, LEVEL )
)
AS stringlist
)
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( d.value, '[^,]+' )
) uniques
FROM data d
) d;
输出:
LIST
---------------------------------------
POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
Oracle设置:
一个小助手功能:
CREATE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
p_result stringlist := stringlist();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
查询3 :
SELECT ( SELECT LISTAGG( COLUMN_VALUE, ',' ) WITHIN GROUP ( ORDER BY ROWNUM )
FROM TABLE( SET( split_String( d.value ) ) ) ) AS list
FROM data d;
或(如果您只想传递一个值):
or (if you only want to pass a single value):
SELECT LISTAGG( COLUMN_VALUE, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS list
FROM TABLE( SET( split_String(
'POW,POW,POWPRO,PRO,PRO,PROUTL,TNEUTL,TNEUTL,UTL,UTLTNE,UTL,UTLTNE'
) ) );
输出:
LIST
---------------------------------------
POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
这篇关于如何在Oracle中通过regexp_replace从逗号分隔的列表中删除重复项?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文