<legend id='61OEU'><style id='61OEU'><dir id='61OEU'><q id='61OEU'></q></dir></style></legend>

      <small id='61OEU'></small><noframes id='61OEU'>

    1. <i id='61OEU'><tr id='61OEU'><dt id='61OEU'><q id='61OEU'><span id='61OEU'><b id='61OEU'><form id='61OEU'><ins id='61OEU'></ins><ul id='61OEU'></ul><sub id='61OEU'></sub></form><legend id='61OEU'></legend><bdo id='61OEU'><pre id='61OEU'><center id='61OEU'></center></pre></bdo></b><th id='61OEU'></th></span></q></dt></tr></i><div id='61OEU'><tfoot id='61OEU'></tfoot><dl id='61OEU'><fieldset id='61OEU'></fieldset></dl></div>

    2. <tfoot id='61OEU'></tfoot>
        <bdo id='61OEU'></bdo><ul id='61OEU'></ul>

      1. Oracle中将字符串拆分为多行

        时间:2023-07-18

          <tbody id='nVzAz'></tbody>

            <bdo id='nVzAz'></bdo><ul id='nVzAz'></ul>

            <small id='nVzAz'></small><noframes id='nVzAz'>

          • <i id='nVzAz'><tr id='nVzAz'><dt id='nVzAz'><q id='nVzAz'><span id='nVzAz'><b id='nVzAz'><form id='nVzAz'><ins id='nVzAz'></ins><ul id='nVzAz'></ul><sub id='nVzAz'></sub></form><legend id='nVzAz'></legend><bdo id='nVzAz'><pre id='nVzAz'><center id='nVzAz'></center></pre></bdo></b><th id='nVzAz'></th></span></q></dt></tr></i><div id='nVzAz'><tfoot id='nVzAz'></tfoot><dl id='nVzAz'><fieldset id='nVzAz'></fieldset></dl></div>

            <tfoot id='nVzAz'></tfoot>
                <legend id='nVzAz'><style id='nVzAz'><dir id='nVzAz'><q id='nVzAz'></q></dir></style></legend>
                1. 本文介绍了Oracle中将字符串拆分为多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我知道 PHP 和 MYSQL 在某种程度上已经解决了这个问题,但我想知道是否有人可以教我在 Oracle 10g(最好)和 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.

                  1. length (regexp_replace(t.error, '[^,]+')) + 1 使用 regexp_replace 删除任何不是分隔符的东西(这里的逗号)case) 和 length +1 来获取有多少元素(错误).
                  2. select level from dual connect by level <= (...) 使用分层查询来创建一个匹配数量不断增加的列发现,从 1 到错误总数.

                  1. 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.
                  2. 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
                  

                2. table(cast(multiset(.....) as sys.OdciNumberList)) 执行一些预言机类型的转换.
                  • 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 作为 nth_appearance/regexp_substr 的 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.
                    • You can add some other columns from your data set (t.name, t.project as an example) for easy visualization.
                    • 一些对 Oracle 文档的引用:

                      Some references to Oracle docs:

                      • REGEXP_REPLACE
                      • REGEXP_SUBSTR
                      • 可扩展性常量、类型和映射 (OdciNumberList)
                      • CAST(多组)
                      • 分层查询

                      这篇关于Oracle中将字符串拆分为多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                3. 上一篇:如何仅从 SQL Server DateTime 数据类型返回日期 下一篇:基于 ID 匹配的从一个表到另一个表的 SQL 更新

                  相关文章

                  <small id='Jh3Vs'></small><noframes id='Jh3Vs'>

                  <legend id='Jh3Vs'><style id='Jh3Vs'><dir id='Jh3Vs'><q id='Jh3Vs'></q></dir></style></legend>
                    <i id='Jh3Vs'><tr id='Jh3Vs'><dt id='Jh3Vs'><q id='Jh3Vs'><span id='Jh3Vs'><b id='Jh3Vs'><form id='Jh3Vs'><ins id='Jh3Vs'></ins><ul id='Jh3Vs'></ul><sub id='Jh3Vs'></sub></form><legend id='Jh3Vs'></legend><bdo id='Jh3Vs'><pre id='Jh3Vs'><center id='Jh3Vs'></center></pre></bdo></b><th id='Jh3Vs'></th></span></q></dt></tr></i><div id='Jh3Vs'><tfoot id='Jh3Vs'></tfoot><dl id='Jh3Vs'><fieldset id='Jh3Vs'></fieldset></dl></div>
                    • <bdo id='Jh3Vs'></bdo><ul id='Jh3Vs'></ul>
                    <tfoot id='Jh3Vs'></tfoot>