[Oracle] 문자열에서 콤마(,) 단위로 행(row)으로 분리하는 쿼리 (콤마별 행분리)

 

참고사항 (먼저확인하기)

REGEXP_로 시작하는 함수(정규식:Regular Expression)는 오라클10g(Oracle10g)부터 지원한다.
이전 버전에서는 사용 할 수 없다.

 

 

---

쿼리 #1 - 열(콤마 단위)을 행으로 조회

-- 문자열에서 콤마(,) 단위로 행(row)으로 분리하는 쿼리 (콤마별 행분리)
select  Regexp_Substr(a.STR,'[^,]+', 1, Level) COL
  from  (
           select 'A1,B123,C12,D123,E334,F1' as STR from dual
        ) a
connect
    by  Regexp_Substr(a.STR, '[^,]+', 1, Level) is not null
;

결과

설명

'aaa, bbb, ccc' 와 같은 문자열을 콤마 단위로 여러행으로 조회하는 쿼리.

 

---

쿼리 #2 - 테이블에서 문자열(콤마 단위)이 들어있는 대상 조회

-- 샘플 #1 -- 컴마로 구분된 값으로 특정 테이블을 조회 할 때 사용
select  S.*
  from  ( -- 샘플 데이터 (테이블이 없으므로..)
          select 'A1'   as C1, '강감찬' as NM from dual union all
          select 'B123' as C1, '이순신' as NM from dual union all
          select 'C2'   as C1, '홍길동' as NM from dual union all
          select 'D3'   as C1, '가나다' as NM from dual union all
          select 'F1'   as C1, '마지막' as NM from dual
         ) S
 where  S.C1 in 
         ( -- in절에 행으로 대입하여 원하는 자료를 조회한다.
           select  Regexp_Substr(a.STR,'[^,]+', 1, Level) COL
             from  (  -- 콤마별로 행으로 만들값
                      select 'A1,B123,C12,D123,E334,F1' as STR from dual
                   ) a
           connect
                by  Regexp_Substr(a.STR, '[^,]+', 1, Level) is not null
         )
;

결과

설명

특정 테이블을 조회할때, in절로 여러개를 한번에 찾으려고한다.
이런 경우 보통 in절을 많이 사용한다.
그러나 콤마(,)가 들어있는 문자열을 그대로 사용 할 수는 없다.
이럴때 행으로 만들어서 in절에 대입하면 해당 대상들만 추출할 수 있다.

---