본문 바로가기

프로그래밍 관련

[오라클] 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( 대상문자열, 패턴, 시작위치, 매칭순번)


ex) 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( 대상문자열, 패턴, 바꿀문자열)


ex) 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 시키는 방법을 써보았습니다.