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

你可以用

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 );