在Oracle中将字符串拆分为多行 [英] Splitting string into multiple rows in Oracle
问题描述
我知道PHP和MYSQL在某种程度上已经回答了这个问题,但是我想知道是否有人可以教我在Oracle 10g(最好是11g)和11g中将字符串(以逗号分隔)分成多行的最简单方法.
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.
表如下:
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
我已经在堆栈周围看到了一些潜在的解决方案,但是它们仅占单个列(以逗号分隔的字符串).任何帮助将不胜感激.
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.
推荐答案
这可能是一种改进的方法(也可以使用regexp和connect by):
This may be an improved way (also with regexp and connect by):
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
编辑: 这是对该查询的简单说明(例如,不深入").
EDIT: Here is a simple (as in, "not in depth") explanation of the query.
-
length (regexp_replace(t.error, '[^,]+')) + 1
使用regexp_replace
擦除不是定界符(在这种情况下为逗号)的任何内容,并使用length +1
来获得其中有多少个元素(错误). -
select level from dual connect by level <= (...)
使用分层查询创建一列,其中找到的匹配项从1到错误总数不断增加.
length (regexp_replace(t.error, '[^,]+')) + 1
usesregexp_replace
to erase anything that is not the delimiter (comma in this case) andlength +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.
预览:
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))
执行一些oracle类型的转换.
-
cast(multiset(.....)) as sys.OdciNumberList
将多个集合(原始数据集中的每一行一个集合)转换为一个数字集合,即OdciNumberList. -
table()
函数将集合转换为结果集.
table(cast(multiset(.....) as sys.OdciNumberList))
does some casting of oracle types.
- The
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. - The
table()
function transforms a collection into a resultset.
FROM
会在数据集和多重集之间创建交叉连接.
结果,数据集中具有4个匹配项的行将重复4次(在名为"column_value"的列中数字递增).
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").
预览:
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))
使用column_value
作为regexp_substr
的 nth_appearance/ocurrence 参数.t.name, t.project
为例),以便于可视化.
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文档的一些引用:
Some references to Oracle docs:
- REGEXP_REPLACE
- REGEXP_SUBSTR
- Extensibility Constants, Types, and Mappings (OdciNumberList)
- CAST (multiset)
- Hierarchical Queries
这篇关于在Oracle中将字符串拆分为多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!