by 김실홍 [2016.08.16 17:27:04]
clob 데이터 지만 간소화 시켜서 설명드리자면
111111#%2222222222#%33333333#%444444#%
clob가 아닌경우 #% 으로 스플릿 해서 간단하게 배열에 넣을수있는데
위데이터가 clob입니다
배열에다가 순서데로 #% 로 자른부분만 넣고싶거든요
dbms_lob 에 있는 함수를 봐도 방법이 떠오르지 않는데 혹시 구현해보신분 계신지요???
WITH t AS ( SELECT 1 idx, TO_CLOB('111111#%2222222222#%33333333#%444444#%') v FROM dual UNION ALL SELECT 2, TO_CLOB('111111#%22222#22222#%3333%3333#%444444#%') FROM dual ) SELECT idx , lv , REGEXP_SUBSTR(REPLACE(v, '#%', '|'), '[^|]+', 1, lv) x FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= REGEXP_COUNT(REPLACE(v, '#%', '|'), '[^|]+') ORDER BY idx, lv ;
아! 구분자를 하나로 합쳐주는 이런 간단한 방법이;;
dbms_lob 함수를 이용하면 이런 방법으로도 가능은 합니다ㅠ
WITH t AS ( SELECT TO_CLOB('111111#%22222#22222#%3333%3333#%444444#%') v FROM dual ) SELECT LV , DECODE(LV,1, DBMS_LOB.SUBSTR(V,DBMS_LOB.INSTR(V,'#%',1,LV) - 1, 1) , DBMS_LOB.SUBSTR(V,DBMS_LOB.INSTR(V,'#%',1,LV) - DBMS_LOB.INSTR(V,'#%',1,LV - 1) - 2, DBMS_LOB.INSTR(V,'#%',1,LV - 1) + 2)) X FROM T , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 99) WHERE LV <= REGEXP_COUNT(V, '#%') ;
WITH t AS ( SELECT 1 idx, TO_CLOB('111111#%2222222222#%33333333#%444444#%') v FROM dual UNION ALL SELECT 2, TO_CLOB('111111#%22222#22222#%3333%3333#%444444#%') FROM dual UNION ALL SELECT 3, TO_CLOB('111111#%2222222222#%#%444444#%') FROM dual ) SELECT idx , lv , SUBSTR(v , INSTR('#%'||v, '#%', 1, lv) , INSTR( v, '#%', 1, lv) - INSTR('#%'||v, '#%', 1, lv) ) x FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= REGEXP_COUNT(v, '#%') ORDER BY idx, lv ;