oracle regexp -列和行的分区值

Question

Oracle DB I在一个单元格中有值-示例：

 ID     TEXT
  1      textText text №1-2-3 texttext, text,text №1 - 1000
         textText text №1-2-3 texttext, text,text №2 - 1030

  2      textText text №1-2-3 texttext, text,text №3 - 1031
         textText text №1-2-3 texttext, text,text №4 - 1032
         textText text №1-2-3 texttext, text,text №5 - 1033

需要选择如下：

 ID     TEXT                                            NUM
  1     textText text №1-2-3 texttext, text,text №1     1000
  1     textText text №1-2-3 texttext, text,text №2     1030
  2     textText text №1-2-3 texttext, text,text №3     1031
  2     textText text №1-2-3 texttext, text,text №4     1032
  2     textText text №1-2-3 texttext, text,text №5     1033

尝试regexp_substr，但我可以将文本、NUM和NUM作为新记录进行分区。我需要分区文本和NUM，然后转到新记录。

我的尝试是：

 select regexp_substr(str, '^[^—]+', 1, level) TEXT,
        regexp_substr(str, '[0-9]+$', 1, level) NUM
 from   (select 'textText text №1-2-3 texttext, text,text №1 — 1000
                 textText text №1-2-3 texttext, text,text №2 — 1030'
         str from dual)
 CONNECT BY regexp_substr(str, '^[^—]+', 1, level) is not null;

结果只有一个字符串，而不是2和NUM --最后一个值，而不是当前值。

Answer 1

with tab(id,txt) as(
 select 1 as id , 'textText text №1-2-3 texttext, text,text №1 - 1000'||CHR(13)||CHR(10)||
                  'textText text №1-2-3 texttext, text,text №2 - 1030' as txt from dual union all
 select 2, 'textText text №1-2-3 texttext, text,text №3 - 1031'||CHR(13)||CHR(10)||
           'textText text №1-2-3 texttext, text,text №4 - 1032'||CHR(13)||CHR(10)||
           'textText text №1-2-3 texttext, text,text №5 - 1033' as txt from dual        


)
select t.*
      ,substr(t.value,instr4(t.value,'-',-1)+2) as num
from (
    select id
         , regexp_substr(txt,'^.*$', 1, level,'m') as value
    from tab
    connect by regexp_substr(txt,'^.*$', 1, level,'m') is not null
    and prior id = id
    and prior sys_guid() is not null
) t

结果

1   textText text ?1-2-3 texttext, text,text ?1 - 1000     1000
1   textText text ?1-2-3 texttext, text,text ?2 - 1030     1030
2   textText text ?1-2-3 texttext, text,text ?3 - 1031     1031
2   textText text ?1-2-3 texttext, text,text ?4 - 1032     1032
2   textText text ?1-2-3 texttext, text,text ?5 - 1033     1033