[오라클 DB] LISTAGG 반대로 콤마로 구분된 문자열 분해하기

오라클에서 LISTAGG 기능을 사용해서 여러개의 ROW를 하나의 문자열로 합치는게 가능하죠. 그렇다면 반대로 하나의 문자열을 구분자로 분리해 여러개의 ROW로 뽑아내는 쿼리는 어떻게 만들 수 있을까요?

일단 오라클에서 기본적으로 LISTAGG와 반대의 기능으로 지원하는 함수는 없고 REGEXP_SUBSTR을 이용해 쿼리를 구성해줘야합니다.

REGEXP를 이용해 생성한 LISTAGG와 반대로 작동하는 쿼리

반응형

LISTAGG로 만든 문자열은 대부분 콤마(,)로 구분되어있는 경우가 많은데요.

예를 들어 '1,2,3,4,5' 라는 문자열을 하나 만들어주고 이 문자열을 콤마로 구분해 각각의 ROW로 리턴하는 쿼리를 예시로 보여드리겠습니다.

SELECT REGEXP_SUBSTR(NUMS,'[^,]+',1,LEVEL) AS NUM
  FROM (SELECT '1,2,3,4,5' AS NUMS FROM DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(NUMS,'[^,]+')) + 1

이렇게 REGEXP_REPLACE와 REGEXP_SUBSTR을 이용한 쿼리를 만들어볼 수 있습니다.

 

간단히 설명하면 CONNECT BY LEVEL은 LEVEL만큼의 ROW를 뽑아내주는 쿼리로 여러 행을 뽑아내기 위해 사용되며 뒤에 오는 부등호 뒤에 있는 NUMBER의 ROW수를 출력해줍니다.

반응형

여기에 LEVEL의 반복 횟수를 찾아주기 위해 REGEXP_REPLACE 기능을 이용해 콤마(,) 뒤에 어떤 문자가 오는 개수를 뽑아주고 첫번째 문자열은 콤마 뒤에 오지 않기 때문에 1을 가산해줍니다.

 

자 그러면 일단 콤마로 구분된 문자열의 개수인 5개의 ROW를 만들어낼 수 있죠.

그리고 나서 REGEXP_SUBSTR을 이용해서 콤마(,)로 구분된 문자열의 LEVEL 번째 항목을 가져오게 됩니다.

이렇게 아래와 같이 총 5개의 ROW로 리턴이 되는거죠.

REGEXP_SUBSTR 결과 화면