正则 - 提取子串

发布时间：2020-12-14 02:24:47 所属栏目：百科来源：网络整理

导读：11g版本： WITH t AS ( SELECT '((:1*:1)+:2)/:10' col FROM dual UNION ALL SELECT '((:3*:0)+:323)/:1213lse12lj' FROM dual ) SELECT col, regexp_substr(col, '[0-9]+' , 1, LEVEL ) AS dd, LEVEL AS position FROM t CONNECT BY LEVEL =regexp_count(co

11g版本：

 
 
  
  WITHtAS
  
  (
  
  SELECT'((:1*:1)+:2)/:10'col
  
  FROMdual
  
  UNIONALL
  
  SELECT'((:3*:0)+:323)/:1213lse12lj'FROMdual
  
  )
  
  SELECTcol,
  
  regexp_substr(col,
  
  '[0-9]+',
  
  1,
  
  LEVEL)ASdd,
  
  LEVELASposition
  
  FROMt
  
  CONNECTBYLEVEL<=regexp_count(col,
  
  '[0-9]+')AND
  
  PRIORdbms_random.VALUEISNOTNULLAND
  
  PRIORcol=col
  
  ORDERSIBLINGSBYcol;

10g版本：

 
 
  
  WITHtAS
  
  (
  
  SELECT'((:1*:1)+:2)/:10'col
  
  FROMdual
  
  UNIONALL
  
  SELECT'((:3*:0)+:323)/:1213lse12lj'FROMdual
  
  )
  
  SELECTcol,
  
  LEVELASposition
  
  FROMt
  
  CONNECTBYLEVEL<=(length(regexp_replace(col,
  
  '[0-9]+',
  
  '~~~'))-
  
  length(REPLACE(regexp_replace(col,
  
  '[0-9]+',
  
  '~~~'),
  
  '~~~')))/3AND
  
  PRIORdbms_random.VALUEISNOTNULLAND
  
  PRIORcol=col
  
  ORDERSIBLINGSBYcol;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!