以下两种情况有很大的不同:拆分单个分隔字符串
为表中的多行拆分分隔字符串。
如果不限制行,则连接从句多行也不会给出想要的输出。对于单个分隔字符串,请参阅
要在表中拆分分隔字符串,请参阅
除了正则表达式,其他一些替代办法是:XMLTable
模型条款
设置SQL> CREATE TABLE t (
2 ID NUMBER GENERATED ALWAYS AS IDENTITY,
3 text VARCHAR2(100)
4 );Table created.SQL>SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');1 row created.SQL> INSERT INTO t (text)
VALUES ('word4, word5, word6');1 row created.SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');1 row created.SQL>
COMMIT;Commit complete.SQL>SQL> SELECT * FROM t;
ID TEXT---------- ----------------------------------------------
1 word1, word2, word3 2 word4, word5, word6 3 word7, word8, word9
SQL>
使用XMLTABLE:SQL> SELECT id,
2 trim(COLUMN_VALUE) text 3 FROM t,
4 xmltable(('"'
5 || REPLACE(text, ',', '","')
6 || '"'))
7 /
ID TEXT---------- ------------------------
1 word1 1 word2 1 word3 2 word4 2 word5 2 word6
3 word7 3 word8 3 word99 rows selected.SQL>
使用模型条款:SQL> WITH
2 model_param AS
3 (
4 SELECT id,
5 text AS orig_str ,
6 ','
7 || text 8 || ','
AS mod_str ,
9 1 AS start_pos ,
10 Length(text) AS end_pos ,
11 (Length(text) - Length(Replace(text, ','))) + 1 AS element_count ,
12 0 AS element_no ,
13 ROWNUM AS rn 14 FROM t )
15 SELECT id,
16 trim(Substr(mod_str, start_pos, end_pos-start_pos)) text 17 FROM (
18 SELECT *
19 FROM model_param MODEL PARTITION BY (id, rn, orig_str, mod_str)
20 DIMENSION BY (element_no)
21 MEASURES (start_pos, end_pos, element_count)
22 RULES ITERATE (2000)
23 UNTIL (ITERATION_NUMBER+1 = element_count[0])
24 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
25 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
26 )
27 WHERE element_no != 0
28 ORDER BY mod_str ,
29 element_no 30 /
ID TEXT---------- --------------------------------------------------
1 word1 1 word2 1 word3 2 word4 2 word5 2 word6
3 word7 3 word8 3 word99 rows selected.SQL>