Oracle提取字符串的问题。

Question

有个需求，需要从一个表的一个字段中提取出需要的字段。比如
原表结构：--还有些字段。我这里就写2个就够了。
statis_date varchar2(8),
content varchar2(511)
..
..
------------------------------------------------------------------
其中content中的数据是这样的：
Main Product  MainProd_A  change to MainProd_B. MainProd_B effect at 20121213 142355. Appendant product list: &. Service list: &. The subscriber IMSI 605123456789  change to 805123456789 . Remaining Core Balance is 2000;-----------------------------
我需要把红色部分分别提取出来插入到新表里面。目前我已经做了个样子，但还有更简单的办法么？求各位给个更简单的办法 ，不然数据多了。我怕性能问题。我的办法如下：
select t.statis_date,
       trim(substr(t.content, 13, instr(t.content, 'change to') - 13)) as oldproduct,
       trim(substr(t.content,
                   instr(t.content, 'to') + 2,
                   instr(t.content, '.') - instr(t.content, 'to') - 2)) as newproduct,
       trim(substr(t.content,
                   instr(t.content, 'at') + 2,
                   instr(t.content, '.', 1, 2) - instr(t.content, 'at') - 2)) as effecttime,
       trim(substr(t.content,
                   instr(t.content, 'IMSI') + 4,
                   instr(t.content, 'change to', 1, 2) - instr(t.content, 'IMSI') - 4)) as oldimsi,
       trim(substr(t.content,
                   instr(t.content, 'change to', 1, 2)+9,
                   instr(t.content, '.', -1, 1) - instr(t.content, 'change to', 1, 2)-9)) as newimsi,
                   trim(substr(t.content,
                   instr(t.content, 'Balance is') + 10,
                   instr(t.content, ';') - instr(t.content, 'Balance is') - 10)) as balance
  from tb_dw_interactlog_his_day t;oracle提取

Answer 1

with test as (
select 'Main Product  MainProd_A  change to MainProd_B. MainProd_B effect at 20121213 142355. Appendant product list: &. Service list: &. The subscriber IMSI 605123456789  change to 805123456789 . Remaining Core Balance is 2000;' as str from dual
)
select trim(regexp_substr(str, '\s\w+', 1, 2)) as oldproduct,
       trim(regexp_substr(str, '\s\w+', 1, 5)) as newproduct,
       regexp_substr(str, '\d+\s\d+', 1, 1) as effecttime,
       regexp_substr(str, '\d+', 1, 3) as oldimsi,
       regexp_substr(str, '\d+', 1, 4) as newimsi,
       regexp_substr(str, '\d+', 1, 5) as balance
  from test

Answer 2

---------------------------------------------------------------------------------
你能说一下 (regexp_substr(str, '\s\w+', 1, 2) 是什么意思吗？
还有 regexp_substr(str, '\d+\s\d+', 1, 1)？

Answer 3

主要是 MainProd_A 和 MainProd_B 并不全是英文，有可能是数字+英文，也有可能全是数据就表示一个mainprod

Answer 4

 
============================================================================================Main Product old11 change to new22. new22 effect at 20121213142355. The subscriber IMSI 605123456789 change to 805123456789 . Remaining Core Balance is 2000. Appendant product list: &. Service list: &.
---------------------------------------------------------------------------------------
Main Product 2523 change to 2562. 2562 effect at 20121213142355. The subscriber IMSI 605123456789 change to 805123456789 . Remaining Core Balance is 2000. Appendant product list: &. Service list: &.
----------------------------------------------------------------------------------------
Main Product MainProd_A change to MainProd_B. MainProd_B effect at 20121213142355. The subscriber IMSI 605123456789 change to 805123456789 . Remaining Core Balance is 2000. Appendant product list: &. Service list: &.
------------------------------------------------------------------------------------------
如上是3种可能的情况。吧红色部分提取数来。

Answer 5

Main Product 2523 change to 2562. 2562 effect at 20121213142355. The subscriber IMSI 605123456789 change to 805123456789 . Remaining Core Balance is 2000. Appendant product list: &. Service list: &.
-------------------------------------------
这种全部纯数字的情况可能没有。但是数字加字母和纯字母的情况是有的。

Answer 6

select trim(regexp_substr(str, '\s\w+', 1, 2)) as oldproduct,
       trim(regexp_substr(str, '\s\w+', 1, 5)) as newproduct,
       ltrim(regexp_substr(str, 'effect at \d+'), 'effect at ') as effecttime,
       ltrim(regexp_substr(str, 'IMSI \d+'), 'IMSI ') as oldimsi,
       rtrim(regexp_substr(str, '\d+\s\.'), ' .') as newimsi,
       ltrim(regexp_substr(str, 'Balance is \d+'), 'Balance is ') as balance
  from test