SQL> select * from t; ID VAL ---------- -------------------- 1 a b c 2 d e f 3 g h iSQL> SQL> select id, substr(val, 1, instr(val, ' ') - 1) 2 from t 3 union 4 select id, substr(val, instr(val, ' ') + 1, 1) 5 from t 6 union 7 select id, substr(val, instr(val, ' ', 1, 2) + 1, 1) from t; ID SUBSTR(VAL,1,INSTR(VAL,'')-1) ---------- ----------------------------- 1 a 1 b 1 c 2 d 2 e 2 f 3 g 3 h 3 i9 rows selected
SQL> select * from t_str_row; ID STR --------------------------------------- -------------------------------- 1 a,b,c 2 a,d,e 3 cSQL> SQL> SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv FROM t_str_row 2 UNION ALL 3 SELECT id, 2 AS p,substr(str,instr(str || ',', ',', 1, 1) + 1,instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv 4 FROM t_str_row 5 UNION ALL SELECT id,3 AS p,substr(str, 6 instr(str || ',', ',', 1, 1) + 1, 7 instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv 8 FROM t_str_row 9 ORDER BY 1, 2; ID P CV ---------- ---------- -------------------------------------------------------------------------------- 1 1 a 1 2 b 1 3 b 2 1 a 2 2 d 2 3 d 3 1 c 3 2 3 3 9 rows selectedSQL>
SQL> select * from t; ID VAL
---------- --------------------
1 a b c
2 d e f
3 g h iSQL>
SQL> select id, substr(val, 1, instr(val, ' ') - 1)
2 from t
3 union
4 select id, substr(val, instr(val, ' ') + 1, 1)
5 from t
6 union
7 select id, substr(val, instr(val, ' ', 1, 2) + 1, 1) from t; ID SUBSTR(VAL,1,INSTR(VAL,'')-1)
---------- -----------------------------
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i9 rows selected
SQL> select * from t_str_row; ID STR
--------------------------------------- --------------------------------
1 a,b,c
2 a,d,e
3 cSQL>
SQL> SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv FROM t_str_row
2 UNION ALL
3 SELECT id, 2 AS p,substr(str,instr(str || ',', ',', 1, 1) + 1,instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
4 FROM t_str_row
5 UNION ALL SELECT id,3 AS p,substr(str,
6 instr(str || ',', ',', 1, 1) + 1,
7 instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
8 FROM t_str_row
9 ORDER BY 1, 2; ID P CV
---------- ---------- --------------------------------------------------------------------------------
1 1 a
1 2 b
1 3 b
2 1 a
2 2 d
2 3 d
3 1 c
3 2
3 3 9 rows selectedSQL>
3 g h i j ...