http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=102&page=1&position=1SoQool에서 퍼왔어요..


글쓴이 : 김홍선


예제 1)

다음과 같은 문자열이 있다.

123,11,4567,8,99999,000,123456

이 문자열을 콤마(,)를 기준으로 행으로 분리해 보자. 즉 아래와 같은 결과가 나오도록 해보자.

123
11
4567
...
...


쿼리는 다음과 같다.


SELECT     SUBSTR (aa,
                   INSTR (aa, ',', 1, LEVEL) + 1,
                   INSTR (aa, ',', 1, LEVEL + 1) - INSTR (aa, ',', 1, LEVEL)
                   - 1
                  ) sub
      FROM (SELECT ',' || '123,11,4567,8,99999,000,123456' || ',' aa
              FROM DUAL)
CONNECT BY LEVEL <= LENGTH (aa) - LENGTH (REPLACE (aa, ',')) - 1



예제 2)

구분기호가 2byte 이상인 일반적인 경우엔 다음과 같이 해준다.

(아래에서 구분기호는 #&&#)


SELECT     SUBSTR (str,
                   INSTR (str, base, 1, LEVEL) + len,
                     INSTR (str, base, 1, LEVEL + 1)
                   - INSTR (str, base, 1, LEVEL)
                   - len
                  ) sub
      FROM (SELECT '#&&#' base, '#&&#' || '1#&(#&#&Ɔ' || '#&&#' str,
                   LENGTH ('#&&#') len
              FROM DUAL)
CONNECT BY LEVEL <= (LENGTH (str) - LENGTH (REPLACE (str, base))) / len - 1



예제 3)


좀 더 응용해 보자.

이번에는 다음과 같이 여러행이 존재할 때,


COL1                                              
--------------------------------------------------
1/2/3                                            
44/555                                            
3/77                                              
8/77/8


/ 를 기준으로 행으로 분리하면, 즉 아래와 같이 나오려면


1
2
3
44
555
3
77
8
77
8


아래와 같이 한다.


SELECT sub
  FROM (SELECT DISTINCT rn, LEVEL,
                        SUBSTR (str,
                                INSTR (str, base, 1, LEVEL) + len,
                                  INSTR (str, base, 1, LEVEL + 1)
                                - INSTR (str, base, 1, LEVEL)
                                - len
                               ) sub
                   FROM (SELECT ROWNUM rn, '/' base, '/' || col1 || '/' str,
                                LENGTH ('/') len
                           FROM tab2)
             CONNECT BY LEVEL <=
                               (LENGTH (str) - LENGTH (REPLACE (str, base)))
                             / len
                           - 1)

같은 문제인데, distinct 를 사용하지 않고 만들어 봤습니다.


WITH table1 AS
     (SELECT 1 c1, 'd2#d3#d4#d4#d6' c2
        FROM DUAL
      UNION ALL
      SELECT 2, 'a2#s3#s4#f4#h6#g4#j6#u8'
        FROM DUAL)
SELECT   c1,
         SUBSTR (str,
                 INSTR (str, base, 1, level#) + len,
                   INSTR (str, base, 1, level# + 1)
                 - INSTR (str, base, 1, level#)
                 - len
                ) c2
    FROM (SELECT c1, '#' base, '#' || c2 || '#' str, LENGTH ('#') len, level#
            FROM table1 a,
                 (SELECT     LEVEL level#
                        FROM (SELECT MAX ((  LENGTH (c2)
                                           - LENGTH (REPLACE (c2, '#'))
                                           + 1
                                          )
                                         ) max#
                                FROM table1) t
                  CONNECT BY LEVEL <= t.max#) b
           WHERE (LENGTH (a.c2) - LENGTH (REPLACE (a.c2, '#')) + 1) >=
                                                                      b.level#)
ORDER BY c1, level#



C1   C2
-------
1    d2
1    d3
1    d4  -- 중복 허용
1    d4  -- 중복 허용
1    d6
2    a2
2    s3
2    s4
2    f4
2    h6
2    g4
2    j6
2    u8