17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from t_str_col;
ID C123
--------------------------------------- --------------------------------
1 v11,v21,v31
2 v12,v22,
3 v13,,v33
4 ,v24,v34
5 v15,,
6 ,,v35
7 ,,
7 rows selected
SQL>
SQL> SELECT id,
2 c123,
3 substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
4 substr(c123,
5 instr(c123 || ',', ',', 1, 1) + 1,
6 instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
7 substr(c123,
8 instr(c123 || ',', ',', 1, 2) + 1,
9 instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
10 FROM t_str_col
11 ORDER BY 1;
ID C123 C1 C2 C3
--------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 v11,v21,v31 v11 v21 v31
2 v12,v22, v12 v22
3 v13,,v33 v13 v33
4 ,v24,v34 v24 v34
5 v15,, v15
6 ,,v35 v35
7 ,,
7 rows selected
SQL>
通用方法:
DROP TABLE test;
CREATE TABLE TEST (x NUMBER,y VARCHAR2(10));
INSERT INTO TEST VALUES(1,'a+b+c');
INSERT INTO TEST VALUES(2,'e+f+g');
--注意取要分几次的时候需要根据'+'的数量加1,如果不加1会丢最后一列
SELECT x,
SUBSTR('+' || y || '+',
INSTR('+' || y || '+', '+', 1, XX.RN) + 1,
INSTR('+' || y || '+', '+', 1, XX.RN + 1) -
INSTR('+' || y || '+', '+', 1, XX.RN) - 1) y
FROM TEST,
(SELECT ROWNUM RN
FROM DUAL
CONNECT BY ROWNUM <=
(SELECT MAX(LENGTH(y) - LENGTH(REPLACE(y, '+')) + 1)
FROM TEST)) XX
WHERE INSTR('+' || y, '+', 1, XX.RN) > 0
ORDER BY x;
--输出:
X Y
1 a
1 b
1 c
2 f
2 g
2 e
CREATE TABLE TEST_ABC (A NUMBER,B VARCHAR2(10));
INSERT INTO TEST_ABC VALUES(1,'a+b+c');
INSERT INTO TEST_ABC VALUES(1,'e+f+g');
INSERT INTO TEST_ABC VALUES(3,'h+i+j+k+l');
COMMIT;
SQL> SELECT * FROM TEST_ABC;
A B
---------- ----------
1 a+b+c
2 e+f+g
3 h+i+j+k+l
-- 1ST WAY:
SQL> SELECT A,
2 SUBSTR('+' || B || '+',
3 INSTR('+' || B || '+', '+', 1, RN) + 1,
4 INSTR('+' || B || '+', '+', 1, RN + 1) -
5 INSTR('+' || B || '+', '+', 1, RN) - 1) "NEW_B"
6 FROM TEST_ABC,
7 (SELECT ROWNUM RN
8 FROM DUAL
9 CONNECT BY ROWNUM <=
10 (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, '+', '')))
11 FROM TEST_ABC)) OB
12 WHERE INSTR(B || '+', '+', 1, RN) > 0;
A NEW_B
---------- ------------------------
1 a
2 e
3 h
1 b
2 f
3 i
1 c
2 g
3 j
3 k
3 l
11 rows selected
-- 2ND WAY:
SQL> SELECT A,
2 SUBSTR('+' || B || '+',
3 INSTR('+' || B || '+', '+', 1, RN) + 1,
4 INSTR('+' || B || '+', '+', 1, RN + 1) -
5 INSTR('+' || B || '+', '+', 1, RN) - 1) "NEW_B"
6 FROM TEST_ABC,
7 (SELECT ROWNUM RN
8 FROM ALL_OBJECTS
9 WHERE ROWNUM <= (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, '+', '')))
10 FROM TEST_ABC) + 1) OB
11 WHERE INSTR(B || '+', '+', 1, RN) > 0;
A NEW_B
---------- ------------------------
1 a
1 b
1 c
2 e
2 f
2 g
3 h
3 i
3 j
3 k
3 l
11 rows selected
SQL>