ORACLE 12C LISTAGG 문자열합치기 중복제거

wm_concat 으로 꿀발다가 ORACLE10 => 12 로 변경되면서 일괄로 문자열 합치기를 수정했던 적이 있다.

 

WM_CONCAT => LISTAGG 로 변경했고 

WM_CONCAT 은 중복제거를 DISTINCT 로 했지만

LISTAGG 는 DISTINCT 가 안되니깐

(19 부터는 된다고 합니다.)

 

결국 구글링 통해 정렬을 이용하여 REGEXP_REPLACE 를 이용하여 처리 했다.

 

항수를 한개 만들고

    PDUP_STR VARCHAR2
    , PDIV VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
    RETURN REGEXP_REPLACE(PDUP_STR, '([^' || PDIV || ']+)(' || PDIV || '\1)*(' || PDIV || '|$)', '\1\3'); 

 

로 처리했음

 

최근 문제가 생긴건 합치려는 열의 값이 , 로 구분되어 여러값이 들어있는 경우였음

이경우에는 listagg 내 order by 가 명확히 작동하지 않아 중복처리가 안되였음

 

with cv_tmp as (
    select '11,22' as tmp  from dual union all
    select '33,22' as tmp  from dual union all
    select '11,33' as tmp  from dual union all
    select '11' as tmp  from dual union all
    select '44,22' as tmp  from dual
)
select listagg(tmp, ',') within group (order by tmp)
    , regexp_replacE(listagg(tmp, ',') within group (order by tmp), '([^,]+)(,\s*\1)+', '\1')   
from cv_tmp a

 

결과값 : 

11,11,22,11,33,33,22,44,22

11,22,11,33,22,44,22

 

방법이 떠오르지 않아서 무식하게 처리함

임시 일련번호 테이블 이용해서 DUP 발생시키고 다시 , 로 분리해서 다시 합치는 걸로 처리함-_-;;;;

 

WITH CV_TMP AS (
    SELECT '11,22' AS TMP  FROM DUAL UNION ALL
    SELECT '33,22' AS TMP  FROM DUAL UNION ALL
    SELECT '11,33' AS TMP  FROM DUAL UNION ALL
    SELECT '11' AS TMP  FROM DUAL UNION ALL
    SELECT '44,22' AS TMP  FROM DUAL
)
, CV_TMP_02 AS (
    SELECT LISTAGG(TMP, ',') WITHIN GROUP (ORDER BY TMP) AS TMP_02
        , REGEXP_REPLACE(LISTAGG(TMP, ',') WITHIN GROUP (ORDER BY TMP), '([^,]+)(,\s*\1)+', '\1')   
    FROM CV_TMP A
)
SELECT REGEXP_REPLACE(LISTAGG(TMP_STR_03, ',') WITHIN GROUP (ORDER BY TMP_STR_03), '([^,]+)(,\s*\1)+', '\1')
FROM (
    SELECT CASE WHEN NO = 1 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 1) 
            WHEN NO = 2 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 2)
            WHEN NO = 3 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 3)
            WHEN NO = 4 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 4)
            WHEN NO = 5 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 5)
            WHEN NO = 6 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 6)
            WHEN NO = 7 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 7)
            WHEN NO = 8 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 8)
            WHEN NO = 9 THEN REGEXP_SUBSTR(TMP_02, '[A-Z0-9-]+', 1, 9)
        END AS TMP_STR_03
    FROM CV_TMP_02 A
        , COPYT B
    WHERE B.NO <= 9
)    

 

COPYT 테이블은 NO 컬럼에 1000 까지 일련번호만 들어있는 테이블임, DUP 발생시키기 위해 사용

대충 간단한 곳에서 사용중이라 다행히 문제는 안되고 있는데

추천할 방법은 아닌것 같기도 하고 

기록용으로 남김 

 

추가로 LISTAGG 추가내용 포함된 포스팅 보여서 참고용으로 추가함

https://studyingazae.tistory.com/15

[ORACLE] 오라클 ORA-01489 에러 LISTAGG 길이 초과 시 해결 방법