我知道有些人已经用PHP和MYSQL回答了这个问题，但我想知道有人能否教我最简单的方法将一个逗号分隔的字符串在Oracle 10g（最好是11g）中拆分成多个行。
表格如下：
Name | Project | Error
108 test Err1, Err2, Err3
109 test2 Err1
我想要创建以下内容：
Name | Project | Error
108 Test Err1
108 Test Err2
108 Test Err3
109 Test2 Err1
我看到了一些在Stack上的潜在解决方案，但它们只考虑了单个列（即逗号分隔的字符串）。任何帮助将不胜感激。
with temp as
(
select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual
union all
select 109, 'test2', 'Err1' from dual
)
select distinct
t.name, t.project,
trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) as error
from
temp t,
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels
order by name
编辑: 这里是一个简单的（即，“不深入”的）查询解释。
length (regexp_replace(t.error, '[^,]+')) + 1 uses regexp_replace to erase anything that is not the delimiter (comma in this case) and length +1 to get how many elements (errors) are there. The select level from dual connect by level <= (...) uses a hierarchical query to create a column with an increasing number of matches found, from 1 to the total number of errors.
Preview:
select level, length (regexp_replace('Err1, Err2, Err3', '[^,]+')) + 1 as max
from dual connect by level <= length (regexp_replace('Err1, Err2, Err3', '[^,]+')) + 1
table(cast(multiset(.....) as sys.OdciNumberList)) does some casting of oracle types.
cast(multiset(.....)) as sys.OdciNumberList transforms multiple collections (one collection for each row in the original data set) into a single collection of numbers, OdciNumberList.table() function transforms a collection into a resultset.FROM without a join creates a cross join between your dataset and the multiset.
As a result, a row in the data set with 4 matches will repeat 4 times (with an increasing number in the column named "column_value").
Preview:
select * from
temp t,
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels
trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) uses the column_value as the nth_appearance/ocurrence parameter for regexp_substr.t.name, t.project as an example) for easy visualization.以下是 Oracle 文档的一些参考链接：
'[^,]+' 的正则表达式用于解析字符串时，如果列表中存在空元素，则无法返回正确的项。有关更多信息，请参见此处: https://dev59.com/D1wZ5IYBdhLWcg3wbv7r#31464699 - Gary_Wregexp_count(t.error, ',')代替length(regexp_replace(t.error, '[^,]+'))，这可能会带来另一种性能提升。 - Štefan Oravec正则表达式是一件非常棒的事情 :)
with temp as (
select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual
union all
select 109, 'test2', 'Err1' from dual
)
SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name
Name的组合都被连接了，如果去掉distinct就可以看到。不幸的是，在connect by子句中添加and Name = prior Name会导致ORA-01436: CONNECT BY loop in user data错误。 - mikAND name = PRIOR name（或者是主键）和 AND PRIOR SYS_GUID() IS NOT NULL 来避免 ORA-01436 错误。 - David Faber下面两者之间有很大的区别：
如果不限制行数，则CONNECT BY子句将产生多行，并且不会产生所需的输出。
除了正则表达式外，还有一些其他选择：
设置
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>
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 word9
9 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 word9
9 rows selected.
SQL>
('"' || REPLACE(text, ',', '","') || '"')，而括号不能被移除吗？Oracle文档（http://docs.oracle.com/database/121/SQLRF/functions268.htm）对我来说不太清楚。这是 XQuery_string 吗？ - Betlista-- Create a collection type to hold the results
CREATE OR REPLACE TYPE typ_str2tbl_nst AS TABLE OF VARCHAR2(30);
/
-- Split the string according to the specified delimiter
CREATE OR REPLACE FUNCTION str2tbl (
p_string VARCHAR2,
p_delimiter CHAR DEFAULT ','
)
RETURN typ_str2tbl_nst PIPELINED
AS
l_tmp VARCHAR2(32000) := p_string || p_delimiter;
l_pos NUMBER;
BEGIN
LOOP
l_pos := INSTR( l_tmp, p_delimiter );
EXIT WHEN NVL( l_pos, 0 ) = 0;
PIPE ROW ( RTRIM( LTRIM( SUBSTR( l_tmp, 1, l_pos-1) ) ) );
l_tmp := SUBSTR( l_tmp, l_pos+1 );
END LOOP;
END str2tbl;
/
-- The problem solution
SELECT name,
project,
TRIM(COLUMN_VALUE) error
FROM t, TABLE(str2tbl(error));
结果：
NAME PROJECT ERROR
---------- ---------- --------------------
108 test Err1
108 test Err2
108 test Err3
109 test2 Err1
Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16336 | 366K| 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 16336 | 366K| 59 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 28 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
尽管集合只有3个值，但优化器估计其有8168行（默认值）。这一开始可能看起来不相关，但这足以让优化器决定采用次优方案。
解决方法是使用优化器扩展为集合提供统计信息：
-- Create the optimizer interface to the str2tbl function
CREATE OR REPLACE TYPE typ_str2tbl_stats AS OBJECT (
dummy NUMBER,
STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
RETURN NUMBER,
STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_string IN VARCHAR2,
p_delimiter IN CHAR DEFAULT ',' )
RETURN NUMBER
);
/
-- Optimizer interface implementation
CREATE OR REPLACE TYPE BODY typ_str2tbl_stats
AS
STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
RETURN NUMBER
AS
BEGIN
p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') );
RETURN ODCIConst.SUCCESS;
END ODCIGetInterfaces;
-- This function is responsible for returning the cardinality estimate
STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_string IN VARCHAR2,
p_delimiter IN CHAR DEFAULT ',' )
RETURN NUMBER
AS
BEGIN
-- I'm using basically half the string lenght as an estimator for its cardinality
p_stats := SYS.ODCITabFuncStats( CEIL( LENGTH( p_string ) / 2 ) );
RETURN ODCIConst.SUCCESS;
END ODCIStatsTableFunction;
END;
/
-- Associate our optimizer extension with the PIPELINED function
ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ_str2tbl_stats;
测试执行计划的结果：
Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 23 | 59 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 1 | 2 | 28 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
以下是同一种情况的几个例子：
SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= regexp_count('Err1, Err2, Err3', ',')+1
/
SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= length('Err1, Err2, Err3') - length(REPLACE('Err1, Err2, Err3', ',', ''))+1
/
另外，可以使用DBMS_UTILITY.comma_to_table和table_to_comma函数： http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table
comma_to_table() 仅适用于符合 Oracle 数据库对象命名约定的标记。例如，它将无法处理像 '123,456,789' 这样的字符串。 - APC从Oracle 12c开始，您可以使用JSON_TABLE和JSON_ARRAY：
CREATE TABLE tab(Name, Project, Error) AS
SELECT 108,'test' ,'Err1, Err2, Err3' FROM dual UNION
SELECT 109,'test2','Err1' FROM dual;
并且查询：
SELECT *
FROM tab t
OUTER APPLY (SELECT TRIM(p) AS p
FROM JSON_TABLE(REPLACE(JSON_ARRAY(t.Error), ',', '","'),
'$[*]' COLUMNS (p VARCHAR2(4000) PATH '$'))) s;
输出：
┌──────┬─────────┬──────────────────┬──────┐
│ Name │ Project │ Error │ P │
├──────┼─────────┼──────────────────┼──────┤
│ 108 │ test │ Err1, Err2, Err3 │ Err1 │
│ 108 │ test │ Err1, Err2, Err3 │ Err2 │
│ 108 │ test │ Err1, Err2, Err3 │ Err3 │
│ 109 │ test2 │ Err1 │ Err1 │
└──────┴─────────┴──────────────────┴──────┘
select
xmltab.txt
from xmltable(
'for $text in tokenize("a,b,c", ",") return $text'
columns
txt varchar2(4000) path '.'
) xmltab
;
如果您的分隔字符串存储在一个或多个表的行中：
select
xmltab.txt
from (
select 'a;b;c' inpt from dual union all
select 'd;e;f' from dual
) base
inner join xmltable(
'for $text in tokenize($input, ";") return $text'
passing base.inpt as "input"
columns
txt varchar2(4000) path '.'
) xmltab
on 1=1
;
REGEXP_COUNT函数是从Oracle 11i开始添加的。以下是一个Oracle 10g的解决方法，采用了Art的解决方案。
SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <=
LENGTH('Err1, Err2, Err3')
- LENGTH(REPLACE('Err1, Err2, Err3', ',', ''))
+ 1;
不使用 connect by 或 regexp:
with mytable as (
select 108 name, 'test' project, 'Err1,Err2,Err3' error from dual
union all
select 109, 'test2', 'Err1' from dual
)
,x as (
select name
,project
,','||error||',' error
from mytable
)
,iter as (SELECT rownum AS pos
FROM all_objects
)
select x.name,x.project
,SUBSTR(x.error
,INSTR(x.error, ',', 1, iter.pos) + 1
,INSTR(x.error, ',', 1, iter.pos + 1)-INSTR(x.error, ',', 1, iter.pos)-1
) error
from x, iter
where iter.pos < = (LENGTH(x.error) - LENGTH(REPLACE(x.error, ','))) - 1;
SELECT LEVEL AS POS FROM DUAL CONNECT BY LEVEL <= 100
使用all_objects有点随机，不太符合我的口味，因为我知道不会超过某个特定值，并且对iter的意图更加明确。 - undefined我遇到了同样的问题，使用xmltable帮助了我:
SELECT id, trim(COLUMN_VALUE) AS text FROM t, xmltable(('"' || REPLACE(text, ',', '","') || '"'))
REGEXP、XMLTABLE和MODEL子句的示例，请参见 使用 Oracle SQL 拆分逗号分隔的字符串表。 - Lalit Kumar BWITH Test AS (SELECT 108 as name, 'test' as project, 'Err1, Err2, Err3' as error UNION ALL SELECT 109, 'test2', 'Err1') SELECT * FROM Test LEFT JOIN LATERAL unnest(string_to_array(error, ', ')) WITH ORDINALITY Error(error, i) ON true;- Gunther Schadow