Oracle listparts deduplication method, oraclelistparts deduplication
1. Use a regular expression to remove duplicates
SELECT regexp_replace('2,2,3,4,5 ', '([^,]+)(,\1)+', '\1') AS col FROM dual
Query results:
SQL> SELECT regexp_replace('2,2,3,4,5 ', '([^,]+)(,\1)+', '\1') AS col 2 FROM dual 3 ; COL--------2,3,4,5
You can sort strings by weight.
2. Use the regular expression above to weight the listparts.
SELECT listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno) deptno, regexp_replace((listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno)), '([^,]+)(,\1)+', '\1') bl_number FROM scott.emp t
Execution result:
SQL> SELECT listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno) deptno, 2 regexp_replace((listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno)), '([^,]+)(,\1)+', '\1') bl_number 3 FROM scott.emp t 4 ; DEPTNO BL_NUMBER-------------------------------------------------------------------------------- --------------------------------------------------------------------------------10,10,10,20,20,20,20,20,30,30,30,30,30,30 10,20,30
The listparts is reordered successfully.