특정구분자로 구성된 문자열을 row로 변환하기(split) 1

데이터베이스에 다음과 같은 키('111,222,333,444,555')를 던져주고 row로 변환하여 다른 테이블과 join, insert 등의 작업을 할 때 사용한다.

SELECT REGEXP_SUBSTR('111,222,333,444,555','[^,]+', 1, LEVEL) 
FROM DUAL
CONNECT BY INSTR('111,222,333,444,555', ',', 1, LEVEL - 1) > 0

응용1

다른 테이블의 테이터와 join 하여 insert를 할 때

INSERT INTO TB_ITEM_FILE
(
  ITEM_CD,
  FILE_ID,
)
SELECT  P_ITEM_CD,    
        A.FILE_ID,    
FROM  TB_COM_FILE_INFO A    
,     ( SELECT REGEXP_SUBSTR('111,222,333,444,555','[^,]+', 1, LEVEL) AS FILE_ID
        FROM DUAL
        CONNECT BY INSTR('111,222,333,444,555', ',', 1, LEVEL - 1) > 0
      ) B
WHERE A.FILE_ID = B.FILE_ID;

응용2

키로 사용하여 조회할 때
 

SELECT 
    A.CENT_CD
,   A.CENT_NM
,   B.CLS_MTH
,   B.NST_CNT  
FROM TB_CENT A 
  ,  TB_CLS B
  ,     ( 
          SELECT REGEXP_SUBSTR('1,2,3,4,5,6,7,44,56','[^,]+', 1, LEVEL) AS CENT_CD
          FROM DUAL
          CONNECT BY INSTR('1,2,3,4,5,6,7,44,56', ',', 1, LEVEL - 1) > 0
      ) C
WHERE A.CENT_CD = B.CENT_CD
AND A.CENT_CD = C.CENT_CD
AND A.USE_YN = 'Y'
AND B.CLS_MTH = '201308'
ORDER BY A.SEQ