blocks|key|181622|text|正则表达式是一件很棒的事情:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|181623|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,+'[%5E,]%2B',+1,+level))+str
++FROM+(SELECT+Name,+Project,+Error+str+FROM+temp)+t
CONNECT+BY+instr(str,+',',+1,+level+-+1)+>+0
order+by+Name|code-block|syntax|javascript|181624|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

regular expressions is a wonderful thing :)

<pre><code>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) &gt; 0
order by Name
</code></pre>

blocks|key|399598|text|下面是几个相同的例子：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|399599|SELECT+trim(regexp_substr('Err1,+Err2,+Err3',+'[%5E,]%2B',+1,+LEVEL))+str_2_tab
++FROM+dual
CONNECT+BY+LEVEL+<=+regexp_count('Err1,+Err2,+Err3',+',')%2B1
/

SELECT+trim(regexp_substr('Err1,+Err2,+Err3',+'[%5E,]%2B',+1,+LEVEL))+str_2_tab
++FROM+dual
CONNECT+BY+LEVEL+<=+length('Err1,+Err2,+Err3')+-+length(REPLACE('Err1,+Err2,+Err3',+',',+''))%2B1
/|code-block|syntax|javascript|399600|此外，还可以使用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|offset|length|399601|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table^0|0|0|1H|2X|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

A couple of more examples of the same:

<pre><code>SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
 FROM dual
CONNECT BY LEVEL &lt;= regexp_count('Err1, Err2, Err3', ',')+1
/

SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
 FROM dual
CONNECT BY LEVEL &lt;= length('Err1, Err2, Err3') - length(REPLACE('Err1, Err2, Err3', ',', ''))+1
/
</code></pre>

Also, may use DBMS_UTILITY.comma_to_table &amp; table_to_comma: 
<a href="http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table" rel="noreferrer">http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table</a>

blocks|key|399624|text|直到Oracle11i才添加REGEXP_COUNT。这是一个Oracle+10g解决方案，取自Art的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|399625|SELECT+trim(regexp_substr('Err1,+Err2,+Err3',+'[%5E,]%2B',+1,+LEVEL))+str_2_tab
++FROM+dual
CONNECT+BY+LEVEL+<=
++LENGTH('Err1,+Err2,+Err3')
++++-+LENGTH(REPLACE('Err1,+Err2,+Err3',+',',+''))
++++%2B+1;|code-block|syntax|javascript|399626|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

REGEXP_COUNT wasn't added until Oracle 11i. Here's an Oracle 10g solution, adopted from Art's solution.

<pre><code>SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
 FROM dual
CONNECT BY LEVEL &lt;=
 LENGTH('Err1, Err2, Err3')
 - LENGTH(REPLACE('Err1, Err2, Err3', ',', ''))
 + 1;
</code></pre>

blocks|key|181710|text|我使用了DBMS_UTILITY.comma_to+_table函数，实际上它的代码工作方式如下|type|unstyled|depth|inlineStyleRanges|entityRanges|data|181711|declare
l_tablen++BINARY_INTEGER;
l_tab+++++DBMS_UTILITY.uncl_array;
cursor+cur+is+select+*+from+qwer;
rec+cur%25rowtype;
begin
open+cur;
loop
fetch+cur+into+rec;
exit+when+cur%25notfound;
DBMS_UTILITY.comma_to_table+(
+++++list+++=>+rec.val,
+++++tablen+=>+l_tablen,
+++++tab++++=>+l_tab);
FOR+i+IN+1+..+l_tablen+LOOP
++++DBMS_OUTPUT.put_line(i+%7C%7C+'+:+'+%7C%7C+l_tab(i));
END+LOOP;
end+loop;
close+cur;
end;+|code-block|syntax|javascript|181712|我使用了我自己的表名和列名|181713|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

i had used the DBMS_UTILITY.comma_to _table function actually its working
the code as follows 

<pre><code>declare
l_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
cursor cur is select * from qwer;
rec cur%rowtype;
begin
open cur;
loop
fetch cur into rec;
exit when cur%notfound;
DBMS_UTILITY.comma_to_table (
 list =&gt; rec.val,
 tablen =&gt; l_tablen,
 tab =&gt; l_tab);
FOR i IN 1 .. l_tablen LOOP
 DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;
end loop;
close cur;
end; 
</code></pre>

i had used my own table and column names

blocks|key|181723|text|我想添加另一个方法。这个问题使用了递归查询，这是我在其他答案中没有见过的。从11gR2开始，Oracle支持此功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|181724|with+cte0+as+(
++++select+phone_number+x
++++from+hr.employees
),+cte1(xstr,xrest,xremoved)+as+(
++++++++select+x,+x,+null
++++++++from+cte0
++++union+all++++++++
++++++++select+xstr,
++++++++++++case+when+instr(xrest,'.')+=+0+then+null+else+substr(xrest,instr(xrest,'.')%2B1)+end,
++++++++++++case+when+instr(xrest,'.')+=+0+then+xrest+else+substr(xrest,1,instr(xrest,'.')+-+1)+end
++++++++from+cte1
++++++++where+xrest+is+not+null
)
select+xstr,+xremoved+from+cte1++
where+xremoved+is+not+null
order+by+xstr|code-block|syntax|javascript|181725|它在拆分字符方面相当灵活。只需在INSTR调用中更改它即可。|offset|length|style|CODE|181726|entityMap^0|0|0|G|5|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I'd like to add another method. This one uses recursive querys, something I haven't seen in the other answers. It is supported by Oracle since 11gR2.

<pre><code>with cte0 as (
 select phone_number x
 from hr.employees
), cte1(xstr,xrest,xremoved) as (
 select x, x, null
 from cte0
 union all 
 select xstr,
 case when instr(xrest,'.') = 0 then null else substr(xrest,instr(xrest,'.')+1) end,
 case when instr(xrest,'.') = 0 then xrest else substr(xrest,1,instr(xrest,'.') - 1) end
 from cte1
 where xrest is not null
)
select xstr, xremoved from cte1 
where xremoved is not null
order by xstr
</code></pre>

It is quite flexible with the splitting character. Simply change it in the <code>INSTR</code> calls.

blocks|key|181792|text|我想提出一种使用流水线化表函数的不同方法。它有点类似于XMLTABLE的技术，只是您提供了自己的自定义函数来拆分字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|181793|--+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+%7C%7C+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%2B1+);
++END+LOOP;
END+str2tbl;
/

--+The+problem+solution
SELECT+name,+
+++++++project,+
+++++++TRIM(COLUMN_VALUE)+error
++FROM+t,+TABLE(str2tbl(error));|code-block|syntax|javascript|181794|结果：|181795|++++++NAME+PROJECT++++ERROR
----------+----------+--------------------
+++++++108+test+++++++Err1
+++++++108+test+++++++Err2
+++++++108+test+++++++Err3
+++++++109+test2++++++Err1|181796|这种方法的问题在于，优化器通常不知道表函数的基数，因此必须进行猜测。这可能会对您的执行计划造成潜在的危害，因此可以扩展此解决方案，为优化器提供执行统计信息。|181797|您可以通过在上面的查询上运行一个EXPLAIN计划来查看这个优化器估计：|181798|Execution+Plan
----------------------------------------------------------
Plan+hash+value:+2402555806

----------------------------------------------------------------------------------------------
%7C+Id++%7C+Operation++++++++++++++++++++++++++%7C+Name++++%7C+Rows++%7C+Bytes+%7C+Cost+(%25CPU)%7C+Time+++++%7C
----------------------------------------------------------------------------------------------
%7C+++0+%7C+SELECT+STATEMENT+++++++++++++++++++%7C+++++++++%7C+16336+%7C+++366K%7C++++59+++(0)%7C+00:00:01+%7C
%7C+++1+%7C++NESTED+LOOPS++++++++++++++++++++++%7C+++++++++%7C+16336+%7C+++366K%7C++++59+++(0)%7C+00:00:01+%7C
%7C+++2+%7C+++TABLE+ACCESS+FULL++++++++++++++++%7C+T+++++++%7C+++++2+%7C++++42+%7C+++++3+++(0)%7C+00:00:01+%7C
%7C+++3+%7C+++COLLECTION+ITERATOR+PICKLER+FETCH%7C+STR2TBL+%7C++8168+%7C+16336+%7C++++28+++(0)%7C+00:00:01+%7C
----------------------------------------------------------------------------------------------|181799|即使该集合只有3个值，优化器也为它估计了8168行(默认值)。乍一看，这似乎无关紧要，但这可能足以让优化器决定一个次优计划。|181800|解决方案是使用优化器扩展为集合提供统计信息：|181801|--+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;|181802|测试生成的执行计划：|181803|Execution+Plan
----------------------------------------------------------
Plan+hash+value:+2402555806

----------------------------------------------------------------------------------------------
%7C+Id++%7C+Operation++++++++++++++++++++++++++%7C+Name++++%7C+Rows++%7C+Bytes+%7C+Cost+(%25CPU)%7C+Time+++++%7C
----------------------------------------------------------------------------------------------
%7C+++0+%7C+SELECT+STATEMENT+++++++++++++++++++%7C+++++++++%7C+++++1+%7C++++23+%7C++++59+++(0)%7C+00:00:01+%7C
%7C+++1+%7C++NESTED+LOOPS++++++++++++++++++++++%7C+++++++++%7C+++++1+%7C++++23+%7C++++59+++(0)%7C+00:00:01+%7C
%7C+++2+%7C+++TABLE+ACCESS+FULL++++++++++++++++%7C+T+++++++%7C+++++2+%7C++++42+%7C+++++3+++(0)%7C+00:00:01+%7C
%7C+++3+%7C+++COLLECTION+ITERATOR+PICKLER+FETCH%7C+STR2TBL+%7C+++++1+%7C+++++2+%7C++++28+++(0)%7C+00:00:01+%7C
----------------------------------------------------------------------------------------------|181804|正如您所看到的，上面计划中的基数不再是8196的猜测值。这仍然是不正确的，因为我们将一个列而不是字符串文字传递给函数。|181805|在这种特殊情况下，有必要对函数代码进行一些调整，以给出更接近的估计，但我认为总体概念在这里已经解释得很清楚了。|181806|本答案中使用的str2tbl函数最初是由Tom+Kyte开发的：https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061|offset|length|181807|通过阅读本文，可以进一步探索将统计数据与对象类型相关联的概念：http://www.oracle-developer.net/display.php?id=427|181808|这里描述的技术在10g%2B中有效。|181809|entityMap|0|LINK|mutability|MUTABLE|url|https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061|1|http://www.oracle-developer.net/display.php?id=427^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|W|29|0|0|V|1E|1|0|0^^$0|@$1|2|3|4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1P|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1R|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1S|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1T|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1U|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1V|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1W|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1X|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|20|8|@]|9|@$16|21|17|22|1|23]]|A|$]]|$1|18|3|19|5|6|7|24|8|@]|9|@$16|25|17|26|1|27]]|A|$]]|$1|1A|3|1B|5|6|7|28|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|29|8|@]|9|@]|A|$]]]|1D|$1E|$5|1F|1G|1H|A|$1I|1J]]|1K|$5|1F|1G|1H|A|$1I|1L]]]]

I would like to propose a different approach using a PIPELINED table function. It's somewhat similar to the technique of the XMLTABLE, except that you are providing your own custom function to split the character string:

<pre><code>-- 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));
</code></pre>

Results:

<pre><code> NAME PROJECT ERROR
---------- ---------- --------------------
 108 test Err1
 108 test Err2
 108 test Err3
 109 test2 Err1
</code></pre>

The problem with this type of approach is that often the optimizer won't know the cardinality of the table function and it will have to make a guess. This could be potentialy harmful to your execution plans, so this solution can be extended to provide execution statistics for the optimizer.

You can see this optimizer estimate by running an EXPLAIN PLAN on the query above:

<pre><code>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 |
----------------------------------------------------------------------------------------------
</code></pre>

Even though the collection has only 3 values, the optimizer estimated 8168 rows for it (default value). This may seem irrelevant at first, but it may be enough for the optimizer to decide for a sub-optimal plan.

The solution is to use the optimizer extensions to provide statistics for the collection:

<pre><code>-- 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;
</code></pre>

Testing the resulting execution plan:

<pre><code>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 |
----------------------------------------------------------------------------------------------
</code></pre>

As you can see the cardinality on the plan above is not the 8196 guessed value anymore. It's still not correct because we are passing a column instead of a string literal to the function. 

Some tweaking to the function code would be necessary to give a closer estimate in this particular case, but I think the overall concept is pretty much explained here.

The str2tbl function used in this answer was originally developed by Tom Kyte:
<a href="https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061" rel="noreferrer">https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061</a>

The concept of associating statistics with object types can be further explored by reading this article:
<a href="http://www.oracle-developer.net/display.php?id=427" rel="noreferrer">http://www.oracle-developer.net/display.php?id=427</a>

The technique described here works in 10g+.

blocks|key|399822|text|下面是一个使用XMLTABLE的替代实现，它允许转换为不同的数据类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|399823|select+
++xmltab.txt
from+xmltable(
++'for+$text+in+tokenize("a,b,c",+",")+return+$text'
++columns+
++++txt+varchar2(4000)+path+'.'
)+xmltab
;|code-block|syntax|javascript|399824|..。或者，如果分隔字符串存储在表的一行或多行中：|399825|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
;|399826|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is an alternative implementation using XMLTABLE that allows for casting to different data types:

<pre><code>select 
 xmltab.txt
from xmltable(
 'for $text in tokenize("a,b,c", ",") return $text'
 columns 
 txt varchar2(4000) path '.'
) xmltab
;
</code></pre>

... or if your delimited strings are stored in one or more rows of a table:

<pre><code>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
;
</code></pre>

blocks|key|399847|text|我也有同样的问题，xmltable帮了我：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|399848|SELECT+id，trim(COLUMN_VALUE)文本FROM+t，xmltable((‘"’%7C%7C+REPLACE(+text，'"，"‘)+%7C%7C+'"'))|399849|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I had the same problem, and xmltable helped me:

SELECT id, trim(COLUMN_VALUE) text
FROM t, xmltable(('"' || REPLACE(text, ',', '","') || '"'))

blocks|key|181881|text|如果您安装了Oracle+APEX+5.1或更高版本，则可以使用方便的APEX_STRING.split功能，例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|181882|select+q.Name,+q.Project,+s.column_value+as+Error
from+mytable+q,
+++++APEX_STRING.split(q.Error,+',')+s|code-block|syntax|javascript|181883|第二个参数是分隔符字符串。它还接受第三个参数，以限制您希望它执行的拆分次数。|181884|https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/SPLIT-Function-Signature-1.html#GUID-3BE7FF37-E54F-4503-91B8-94F374E243E6|181885|entityMap|0|LINK|mutability|MUTABLE|url^0|Z|H|0|0|0|0|43|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Z|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|10|8|@]|D|@$9|11|A|12|1|13]]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|N]]]]

If you have Oracle APEX 5.1 or later installed, you can use the convenient <code>APEX_STRING.split</code> function, e.g.:
<pre><code>select q.Name, q.Project, s.column_value as Error
from mytable q,
 APEX_STRING.split(q.Error, ',') s
</code></pre>
The second parameter is the delimiter string. It also accepts a 3rd parameter to limit how many splits you want it to perform.
<a href="https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/SPLIT-Function-Signature-1.html#GUID-3BE7FF37-E54F-4503-91B8-94F374E243E6" rel="nofollow noreferrer">https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/SPLIT-Function-Signature-1.html#GUID-3BE7FF37-E54F-4503-91B8-94F374E243E6</a>

I know this has been answered to some degree with PHP and MYSQL, but I was wondering if someone could teach me the simplest approach to splitting a string (comma delimited) into multiple rows in Oracle 10g (preferably) and 11g. 

The table is as follows:

<pre><code>Name | Project | Error 
108 test Err1, Err2, Err3
109 test2 Err1
</code></pre>

I want to create the following:

<pre><code>Name | Project | Error
108 Test Err1
108 Test Err2 
108 Test Err3 
109 Test2 Err1
</code></pre>

I've seen a few potential solutions around stack, however they only accounted for a single column (being the comma delimited string). Any help would be greatly appreciated.

Splitting string into multiple rows in Oracle

我知道PHP和MYSQL在某种程度上回答了这个问题，但我想知道是否有人可以教我在Oracle 10g (最好是)和11g中将字符串(逗号分隔)拆分为多行的最简单方法。如下表所示：Name | Project | Error 108    test      Err1, Err2, Err3109    test2   ...

问在Oracle中将字符串拆分为多行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle中将字符串拆分为多行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle中将字符串拆分为多行
EN