How to Get the details after the space in REGEXP_SUBSTR in oracle 11g

Question

I just want some help in my script. I have a 2 types of values in my data the first one is

first-order, Delivery Date: 04/18/2020 AM, OOS: Find similar item

The other one is

third-order, Delivery Date:04/19/2020 AM, OOS: Find similar item

I already get the "04/19/2020" after the word "Delivery Date:" in the second example. My problem is how can I get the date "04/18/2020" after the word "Delivery Date:" in the first value. Here is what I came up to get the value of the second example.

select REGEXP_SUBSTR(column_name,'Date:([[:alnum:]]+\.?){1,10}........?') from mytable

What other way to get the value after the space or additional function in my existing script. Thank you. Appreciate your help.

Answer 1

Use the following regex pattern:

Delivery Date:\s*(\S+)

This matches the contents after delivery date, also allowing for an optional space separator after the colon.

SQL code:

SELECT
    col,
    REGEXP_SUBSTR(col, 'Delivery Date:\s*(\S+)', 1, 1, NULL, 1) AS "date"
FROM mytable;

Demo