REGEXP_SUBSTR함수로.SPLIT하기

 

 

[Oracle]REGEXP_SUBSTR함수로.SPLIT하기

 

 

..

WITH X AS
( SELECT 'NAVER' AS USER_ID ,'NA1,NA2,NA3,NA4,NA5,NA6,' AS RE_CODE FROM DUAL
   UNION ALL
  SELECT 'NAVER1' AS USER_ID ,'NA7,NA8,NA9,NA10' AS RE_CODE FROM DUAL

)

SELECT REGEXP_SUBSTR(A.RE_CODE, '[^,]+', 1, B.LV) AS RE_CODE
  FROM X A
      ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) B
 WHERE 1 = 1
   AND B.LV <= REGEXP_COUNT(A.RE_CODE, ',') + 1
   AND UPPER(A.USER_ID) = 'NAVER'
  ;


.

1. 테이블 X의 데이타

 

No	USER_ID	RE_CODE
1	NAVER	NA1,NA2,NA3,NA4,NA5,NA6,
2	NAVER1	NA7,NA8,NA9,NA10

쿼리 실행 결과

 

No	RE_CODE
1	NA1
2	NA2
3	NA3
4	NA4
5	NA5
6	NA6
7