Oracle 12c - Extract dates from strings and get the difference of No.of.Days

Question

I've these dynamic strings:

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

How can I extract dates and get the difference of No.of.Days between P1_date and P2_Date (ignore time part)?

I tried with SUBSTR, INSTR but dates not getting extracted due to position values are dynamic in the string.

Barbaros Özhan · Answer 1 · 2019-11-10T11:00:47.263

You can use regexp_replace() and then date conversion by 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 · Answer 2 · 2019-11-10T13:04:31.403

You can use regexp_substr to extract only the 3rd and 4th comma-separated strings, using the subexpression parameter to get the 2nd colon-delimited word within that:

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

The subexpression parameter works with expressions containing subexpressions e.g. (x)(y)(z), where subexpression 2 would be y.

The ? in place of + in my p1_date expression was a bit of a guess to be honest, as using [^:]+ stripped the final character.

Once you have that, you can convert to dates as normal:

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;

Or it might be more readable with the regex parsing and date conversion nested within an inline view/CTE, so that you can refer more simply to p1_date and p2_date in an outer query:

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

Error messages are dynamic i may get with all commas or without any commas or partial commas. But colons are remained stay and intact. I've updated the question. Hence i can't fully rely on comma-separated strings. — venkat, Nov 10 '19 at 13:52
Thanks for your solution it helps for all comma-separated string related error messages. Please do not remove this. — venkat, Nov 10 '19 at 13:59

Oracle 12c - Extract dates from strings and get the difference of No.of.Days

2 Answers2