[오라클] split to row 구현

오라클에서 문자열을 split 한 후 그 결과를 로우로 리턴 받고 싶은 경우가 있을 수 있습니다.

예를 들어

 

123▨9214▨432▨567▨8867

 

와 같은 문자열을

 

123

9214

432

567

8867

 

와 같은 형태로 변경해야 할 때가 있습니다.

그러나 오라클에는 split 함수가 존재하지 않고, 결과가 다중 로우로 떨어지기에 별도의 함수를 만들기도 어렵습니다.

 

이럴 때 쓸만한 방법을 만들어 보았으니 필요하신 분들께 도움이 되었으면 합니다.

 

아이디어 1

오라클 10g 부터 지원하는 regexp_substr 함수를 활용하면 쉽게 구분자 사이의 값을 가져 올 수 있습니다.

regexp_substr 참고 -> https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions138.htm#SQLRF06303

 

regexp_substr( 대상문자열, 패턴, 시작위치, 매칭순번)

select regexp_substr('123▨9214▨432▨567▨8867', '[^▨]+', 1, 3) from dual

--> 432

 

참고로 [^▨]+ 이 정규식 표현은 ▨이 아닌 문자열을 뜻합니다.

 

아이디어 2

결과 로우 수 계산

regexp_replace 참고 -> https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions137.htm#SQLRF06302

 

regexp_replace( 대상문자열, 패턴, 바꿀문자열)

select regexp_replace('123▨9214▨432▨567▨8867', '[^▨]+', '') from dual

-->▨▨▨▨

 

참고로 바꿀문자열을 생략하면 찾은문자열을 대상문자열에서 지워버립니다.

주의 할 점은 만약 대상문자열에 구분자가 없는 경우 

'123'

결과는 null이 된다는 것입니다.

select length(nvl(regexp_replace('123▨9214▨432▨567▨8867', '[^▨]+', ''), 0)) + 1
  from dual

--> 5

 

 

아이디어 3

오라클 결과에 재귀를 돌릴 수 있는 connect by 구문을 사용하면 더미 row를 만들 수 있습니다.

select '123▨9214▨432▨567▨8867' as str from dual
connect by rownum <= 3

 

-->

123▨9214▨432▨567▨8867

123▨9214▨432▨567▨8867

123▨9214▨432▨567▨8867

 

 

* 결과

select regexp_substr(a.mydatas,'[^▨]+', 1, rownum) as mydata
  from (select '123▨9214▨432▨567▨8867' as mydatas from dual) a
connect by rownum <= length(regexp_replace(mydatas,'[^▨]+')) + 1

--->

123

9214

432

567

8867

 

위 쿼리의 제약사항은

 - split을 실행 할 대상 row가 단일행이어야 합니다. 다시 말해 from 절 서브쿼리 a의 결과가 1행이어야 합니다.

   connect by 이후 where 절이 실행 되므로  from 절 서브쿼리로 단일행의 결과를 만든 후 작업을 진행하지 않으면 대상 테이블을 full scan 합니다.

 

 

그럼 여기서 한 단계 더 나가서 다중행 결과를 split 처리 하려면?

 

!!!  다중행 split to row !!!

select a.myid
     , b.seq 
     , regexp_substr(a.mydatas,'[^▨]+', 1, b.seq) as mydata
from (select origin.myid
           , origin.mydatas
           , nvl(LENGTH(regexp_REPLACE(origin.mydatas,'[^▨]+')), 0) + 1 cnt
       from (select 'ab' as myid, '123▨921432▨567' as mydatas from dual union
             select 'er' as myid, '914' as mydatas from dual union
             select 'ff' as myid, '924▨4302' as mydatas from dual ) origin) a
     , (select rownum seq
          from dual
        CONNECT BY ROWNUM <= 100) b
 where b.seq <= a.cnt
order by a.myid, b.seq

-->

ab	1	1	123
ab	1	2	921432
ab	1	3	567
er	2	1	914
ff	3	1	924
ff	3	2	4302

 

 

요런 식으로 하시면 되겠습니다.

from 절의 b가 더미 테이블로 'CONNECT BY ROWNUM <= 100' 이 부분의 숫자를 조절하여 최대 결과 row 수를 조절 하시면 되겠습니다.

 

그럼 즐 코딩!!!

 

 

ps. 만약 값이 비어서 구분자가 곂쳐 있다면.. 예를 들어

'123▨9214▨▨▨432▨567▨8867'

중간에 null 값을 건너 띄고 값을 가져오는 오류가 발생합니다.

피하는 방법이 여러가지 있을 텐데 저는 쉽게 

trim(regexp_substr(replace(a.mydatas, '▨', '▨ '),'[^▨]+', 1, rownum))

요런식으로 구분자+스페이스로 바꾸고 trim 시키는 방법을 써보았습니다.