데이터베이스 질문과 답변
WITH TT AS ( SELECT 'NA1|ND1' AS AB FROM DUAL UNION ALL SELECT 'NA1|NB2' FROM DUAL UNION ALL SELECT 'NA1|NG1|NG7' FROM DUAL ) SELECT LISTAGG(AB, '|' ) WITHIN GROUP(ORDER BY AB) FROM TT NA1|NB2|NA1|ND1|NA1|NG1|NG7 아래와 같은 결과가 나오는데, 어떤식으로 처리를 해줘야 레코드에서 중복처리를 방지할수있을까요?
AB에 대한 값은 다양하게 들어있어서 REPLACE 와 같은부분으로는 좀 어렵습니다..
정규식은 잘몰라서 어려움을 겪고있습니다ㅠ
도와주세요
by 마농
[2018.03.29 17:35:44]
WITH tt AS
(
SELECT 'NA1|ND1' ab FROM dual
UNION ALL SELECT 'NA1|NB2' FROM dual
UNION ALL SELECT 'NA1|NG1|NG7' FROM dual
)
SELECT LISTAGG(v, '|') WITHIN GROUP(ORDER BY v) ab
FROM (SELECT DISTINCT REGEXP_SUBSTR(ab, '[^|]+', 1, lv) v
FROM tt
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
WHERE lv <= LENGTH(ab) - LENGTH(REPLACE(ab, '|')) + 1
)
;
by 오라클네티
[2018.03.29 17:46:30]
답변 주셔서 감사합니다.
by 우리집아찌
[2018.03.29 17:41:52]
WITH TT AS (
SELECT 'NA1|ND1' AS AB FROM DUAL
UNION ALL
SELECT 'NA1|NB2' FROM DUAL
UNION ALL
SELECT 'NA1|NG1|NG7' FROM DUAL
) , TT2 AS (
SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 3
)
SELECT LISTAGG(AA, '|' ) WITHIN GROUP(ORDER BY AA)
FROM (SELECT DISTINCT REGEXP_SUBSTR(A.AB,'[^|]+',1,LV ) AA
FROM TT A
, TT2 B
WHERE LV <= REGEXP_COUNT(A.AB,'[|]')+1
)
by 오라클네티
[2018.03.29 17:46:41]
답변 주셔서 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.