SQLTIP－－文字列分割

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)
区分記号が2バイト以上の場合


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)