다음과 같은 데이타가 있을때요
'TEXT' 컬럼의 중복된 데이타를 없애고 출력하고 싶습니다.
SELECT 'A' 구분,'경기#경기#전남#서울' TXET FROM DUAL UNION ALL
SELECT 'B' 구분,'경기#경기' TXET FROM DUAL UNION ALL
SELECT 'C' 구분,'경기#경기#인천#광주' TXET FROM DUAL UNION ALL
SELECT 'D' 구분,'경기#전남#전남' TXET FROM DUAL UNION ALL
SELECT 'E' 구분,'경기#강원#울산#서울#광주#인천' TXET FROM DUAL UNION ALL
SELECT 'F' 구분,'인천' TXET FROM DUAL
== 결과값 ===
A 경기#전남#서울
B 경기
C 경기#인천#광주
D 경기#전남
E 경기#강원#울산#서울#광주#인천
F 인천
1 2 3 4 5 6 7 8 9 10 | WITH T AS ( SELECT 'A' 구분, '경기#경기#전남#서울' TEXT FROM DUAL UNION ALL SELECT 'B' 구분, '경기#경기' TEXT FROM DUAL UNION ALL SELECT 'C' 구분, '경기#경기#인천#광주' TEXT FROM DUAL UNION ALL SELECT 'D' 구분, '경기#전남#전남' TEXT FROM DUAL UNION ALL SELECT 'E' 구분, '경기#강원#울산#서울#광주#인천' TEXT FROM DUAL UNION ALL SELECT 'F' 구분, '인천' TEXT FROM DUAL ) SELECT 구분, REGEXP_REPLACE(TEXT, '([^#]+)(#\1)+' , '\1' ) TEXT FROM T |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | WITH t AS ( SELECT 'A' 구분, '경기#경기#전남#서울' text FROM dual UNION ALL SELECT 'B' , '경기#경기' FROM dual UNION ALL SELECT 'C' , '경기#경기#인천#광주' FROM dual UNION ALL SELECT 'D' , '경기#전남#전남' FROM dual UNION ALL SELECT 'E' , '경기#강원#울산#서울#광주#인천' FROM dual UNION ALL SELECT 'F' , '인천' FROM dual UNION ALL SELECT 'G' , '경기#경기#인천#광주#경기' FROM dual ) SELECT 구분 , LISTAGG(text, '#' ) WITHIN GROUP ( ORDER BY lv) text FROM ( SELECT 구분 , REGEXP_SUBSTR(text, '[^#]+' , 1, lv) text , MIN (lv) lv FROM t , ( SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) WHERE lv <= REGEXP_COUNT(text, '[^#]+' ) GROUP BY 구분 , REGEXP_SUBSTR(text, '[^#]+' , 1, lv) ) GROUP BY 구분 ORDER BY 구분 ; |