代码之家 › 专栏 › 技术社区 › venkat

Oracle 12c-选择冒号后的日期时间字符串

toad oracle12c oracle

venkat · 技术社区 · 5 年前

我有这些动态字符串:

Error: P1_Date > P2_Date, Serial_Number:824354334344332, P2_Date:11/17/2019 6:07:00 PM, P1_Date:11/18/2019

Error: P1_Date > P2_Date Serial_Number:7777734, P2_Date:11/27/2019 8:47:00 PM P1_Date:11/29/2019

Error: P1_Date > P2_Date Serial_Number:9788871212 P2_Date:11/25/2019 12:14:05 PM P1_Date:1/8/2020

如何提取日期,并获取P1廑u日期和P2廑u日期之间的天数差(忽略时间部分)?

我试过使用SUBSTR,INSTR,但是由于位置值在字符串中是动态的,所以没有提取日期。

0 回复 | 直到 5 年前

Barbaros Özhan 5 年前

你可以用 regexp_replace() to_date(<date_column>,'mm/dd/yyyy') :

with t as
(
select regexp_replace(str,'(.*P2_Date:)(.*)( )(.*)','\2') as P2_Date, 
       regexp_replace(str,'(.*P1_Date:)(.*)$','\2') as P1_Date, 
       t.*
  from tab t -- "tab" is assumed to be your original table
)
select to_date(substr(P2_Date,1,instr(P2_Date,' ')),'mm/dd/yyyy') -
       to_date(P1_Date,'mm/dd/yyyy') "Date Difference"           
  from t

Demo

William Robertson 5 年前

你可以用 regexp_substr

with demo (str) as
     ( select 'Error: P1_Date > P2_Date, Serial_Number:824354334344332, P2_Date:11/17/2019 6:07:00 PM, P1_Date:11/18/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:7777734, P2_Date:11/27/2019 8:47:00 PM, P1_Date:11/29/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:9788871212, P2_Date:11/25/2019 12:14:05 PM, P1_Date:1/8/2020' from dual
     )
select regexp_substr(str, '(:)([^,]+)([^:]?)',1,4,null,2) as p1_date
     , regexp_substr(str, '(:)([^,]+)([^:]+)',1,3,null,2) as p2_date
from   demo;

P1_DATE                P2_DATE
---------------------- ----------------------
11/18/2019             11/17/2019 6:07:00 PM
11/29/2019             11/27/2019 8:47:00 PM
1/8/2020               11/25/2019 12:14:05 PM

subexpression参数用于包含子表达式的表达式,例如。 (x)(y)(z) y .

? + 在我的 p1_date [^:]+ 去掉了最后一个字符。

with demo (str) as
     ( select 'Error: P1_Date > P2_Date, Serial_Number:824354334344332, P2_Date:11/17/2019 6:07:00 PM, P1_Date:11/18/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:7777734, P2_Date:11/27/2019 8:47:00 PM, P1_Date:11/29/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:9788871212, P2_Date:11/25/2019 12:14:05 PM, P1_Date:1/8/2020' from dual
     )
select to_date(regexp_substr(str, '(:)([^,]+)([^:]?)',1,4,null,2), 'MM/DD/YYYY') as p1_date
     , to_date(regexp_substr(str, '(:)([^,]+)([^:]+)',1,3,null,2), 'MM/DD/YYYY HH:MI:SS PM') as p2_date
     , to_date(regexp_substr(str, '(:)([^,]+)([^:]?)',1,4,null,2), 'MM/DD/YYYY') -
       to_date(regexp_substr(str, '(:)([^,]+)([^:]+)',1,3,null,2), 'MM/DD/YYYY HH:MI:SS PM') as diff
from   demo;

或者,对于嵌套在内联视图/CTE中的regex解析和日期转换,它可能更具可读性,以便您可以更简单地引用 p1_日期 和 p2_date

with demo (str) as
     ( select 'Error: P1_Date > P2_Date, Serial_Number:824354334344332, P2_Date:11/17/2019 6:07:00 PM, P1_Date:11/18/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:7777734, P2_Date:11/27/2019 8:47:00 PM, P1_Date:11/29/2019' from dual union all
       select 'Error: P1_Date > P2_Date, Serial_Number:9788871212, P2_Date:11/25/2019 12:14:05 PM, P1_Date:1/8/2020' from dual
     )
select p1_date
     , p2_date
     , p1_date - p2_date as diff
from   ( select to_date(regexp_substr(str, '(:)([^,]+)([^:]?)',1,4,null,2), 'MM/DD/YYYY') as p1_date
              , to_date(regexp_substr(str, '(:)([^,]+)([^:]+)',1,3,null,2), 'MM/DD/YYYY HH:MI:SS PM') as p2_date
         from   demo );

REGEXP_SUBSTR