|
|
相关文章推荐
|
苦闷的木瓜 · TypeScript清空数组的4种方法-阿里 ...· 3 月前 · |
|
|
心软的红金鱼 · reactjs - How to use ...· 1 年前 · |
|
|
读研的人字拖 · matlab,edit换行_matlab ...· 1 年前 · |
我知道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我已经看到了一些关于堆栈的潜在解决方案，但它们只占单个列(逗号分隔的字符串)。任何帮助都将不胜感激。
9 个回答
高票数
最新
Stack Overflow用户
回答于2013-01-15 12:12
得票数
32
正则表达式是一件很棒的事情:)
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
Stack Overflow用户
回答于2013-01-18 04:27
得票数
8
下面是几个相同的例子：
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
Stack Overflow用户
回答于2014-07-22 04:20
得票数
4
直到Oracle11i才添加REGEXP_COUNT。这是一个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;
Stack Overflow用户
修改于2014-10-21 14:25
得票数
-1
我使用了DBMS_UTILITY.comma_to _table函数，实际上它的代码工作方式如下
declare
l_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
cursor cur is select * from qwer;
rec cur%rowtype;
begin
open cur;
fetch cur into rec;
exit when cur%notfound;
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 || ' : ' || l_tab(i));
END LOOP;
end loop;
close cur;
end; 我使用了我自己的表名和列名
Stack Overflow用户
回答于2015-08-17 21:54
得票数
2
我想添加另一个方法。这个问题使用了递归查询，这是我在其他答案中没有见过的。从11gR2开始，Oracle支持此功能。
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
它在拆分字符方面相当灵活。只需在
INSTR
调用中更改它即可。
Stack Overflow用户
回答于2016-10-27 21:55
得票数
7
我想提出一种使用流水线化表函数的不同方法。它有点类似于XMLTABLE的技术，只是您提供了自己的自定义函数来拆分字符串：
-- 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
l_tmp VARCHAR2(32000) := p_string || p_delimiter;
l_pos NUMBER;
BEGIN
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这种方法的问题在于，优化器通常不知道表函数的基数，因此必须进行猜测。这可能会对您的执行计划造成潜在的危害，因此可以扩展此解决方案，为优化器提供执行统计信息。
您可以通过在上面的查询上运行一个EXPLAIN计划来查看这个优化器估计：
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
STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
RETURN NUMBER
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
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;
-- 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 |
----------------------------------------------------------------------------------------------正如您所看到的，上面计划中的基数不再是8196的猜测值。这仍然是不正确的，因为我们将一个列而不是字符串文字传递给函数。
在这种特殊情况下，有必要对函数代码进行一些调整，以给出更接近的估计，但我认为总体概念在这里已经解释得很清楚了。
本答案中使用的str2tbl函数最初是由Tom Kyte开发的： https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061
通过阅读本文，可以进一步探索将统计数据与对象类型相关联的概念： http://www.oracle-developer.net/display.php?id=427
这里描述的技术在10g+中有效。
Stack Overflow用户
修改于2017-12-01 22:57
得票数
4
下面是一个使用XMLTABLE的替代实现，它允许转换为不同的数据类型：
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
推荐文章