DATOR


한 컬럼내의 구분자로 연결된 문자열을 종으로(로우로) 분리하는 쿼리(1/3) Oracle


아래와 같은 데이타를 갖는 테이블이 있다고 가정을 하고, NAME 컬럼에 있는 문자열은 '|' 구분자로

구분되어진 문자열의 나열이라고 했을때 구분자를 기준으로 문자열을 뽑아내어 문자열 개수만큼의

로우 데이터로 변환하는 쿼리를 작성할 수 있다.

 

원본 테이블 데이타

NUM NAME

1      'A1|B1'

2      'A2|B2|C2'

3      'A3|B3|C3|D3'

 

A 인라인뷰는 최대 구분자 개수 만큼의 숫자 셋이 필요하다.

여기서는 '|'구분자가 제일많이 사용된 문자열이 4개 이므로 4개의 숫자 셋을 생성하였다.

 

SELECT
    SUBSTR(B.NAME,DECODE(NO, 1, 1, DECODE(INSTR(B.NAME,'|', 1, NO),0,LENGTH(B.NAME)+1,INSTR(B.NAME,'|',1,NO))
        - DECODE(NO,1, INSTR(B.NAME,'|',1,1)-1, LENGTH(B.NAME)-LENGTH(REPLACE(B.NAME,'|'))+1
        , LENGTH(B.NAME)-INSTR(B.NAME,'|',1,LENGTH(B.NAME)-LENGTH (REPLACE(B.NAME,'|')))
        , INSTR(B.NAME,'|',1,NO)-INSTR(B.NAME,'|',1,NO-1)-1
        )
      )
      , DECODE(NO,1, INSTR(B.NAME,'|',1,1)-1
        , LENGTH(B.NAME)-LENGTH(REPLACE(B.NAME,'|'))+1
        , LENGTH(B.NAME) - INSTR(B.NAME,'|',1,LENGTH(B.NAME)-LENGTH (REPLACE(B.NAME,'|')))
        , INSTR(B.NAME,'|',1,NO)-INSTR(B.NAME,'|',1,NO-1)-1
      )
    ) WORD
    , A.NO

    , B.NUM
  FROM (SELECT 1 NO FROM DUAL
        UNION ALL
        SELECT 2 NO FROM DUAL
        UNION ALL
        SELECT 3 NO FROM DUAL
        UNION ALL
        SELECT 4 NO FROM DUAL

        ) A

     , ( SELECT 1 NUM, 'A1|B1' NAME FROM DUAL
         UNION ALL
         SELECT 2, 'A2|B2|C2' NAME FROM DUAL
         UNION ALL
         SELECT 3, 'A3|B3|C3|D3' NAME FROM DUAL
       ) B
WHERE NO <= (LENGTH(B.NAME)-LENGTH(REPLACE(B.NAME,'|'))+1)

 

결과 데이타

Word No Num

A1     1     1
B1     2     1
A2     1     2
B2     2     2
C2     3     2
A3     1     3
B3     2     3
C3     3     3
D3     4     3

TAG

Leave Comments